/*
 * @(#)WorkflowRecord.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.workflow.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Vector;

import com.dcivision.dms.dao.DmsDocumentDAObject;
import com.dcivision.form.bean.FormRecord;
import com.dcivision.form.dao.FormRecordDAObject;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ApplicationExceptionList;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.PermissionManager;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.dao.SysUserDefinedIndexDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.user.bean.UserGroup;
import com.dcivision.user.bean.UserRole;
import com.dcivision.user.dao.UserGroupDAObject;
import com.dcivision.user.dao.UserRoleDAObject;
import com.dcivision.workflow.WorkflowErrorConstant;
import com.dcivision.workflow.bean.MtmWorkflowStepUserActor;
import com.dcivision.workflow.bean.MtmWorkflowStepWorkflowStep;
import com.dcivision.workflow.bean.WorkflowRecord;
import com.dcivision.workflow.bean.WorkflowStep;
import com.dcivision.workflow.core.SystemWorkflowConstant;
import com.dcivision.workflow.web.ListWorkflowRecordForm;

/**
 * WorkflowRecordDAObject.java
 * 
 * This class is the data access bean for table "WORKFLOW_RECORD".
 * 
 * @author Angus Shiu
 * @company DCIVision Limited
 * @creation date 04/08/2003
 * @version $Revision: 1.103.2.2 $
 */

public class WorkflowRecordDAObject extends AbstractDAObject {

	public static final String REVISION = "$Revision: 1.103.2.2 $";

	public static final String TABLE_NAME = "WORKFLOW_RECORD";

	public WorkflowRecordDAObject(SessionContainer sessionContainer, Connection dbConn) {
		super(sessionContainer, dbConn);
	}

	protected void initDBSetting() {
		this.baseTableName = TABLE_NAME;
		this.vecDBColumn.add("ID");
		this.vecDBColumn.add("WORKFLOW_CODE");
		this.vecDBColumn.add("WORKFLOW_VERSION");
		this.vecDBColumn.add("WORKFLOW_GROUP_ID");
		this.vecDBColumn.add("ALLOW_INTERRUPT");
		this.vecDBColumn.add("INTERRUPT_WORKFLOW_RECORD_ID");
		this.vecDBColumn.add("STATUS");
		this.vecDBColumn.add("START_DATE");
		this.vecDBColumn.add("END_DATE");
		this.vecDBColumn.add("DESCRIPTION");
		this.vecDBColumn.add("WORKFLOW_CATEGORY_ID");
		this.vecDBColumn.add("DMS_PARENT_ID");
		this.vecDBColumn.add("RECORD_STATUS");
		this.vecDBColumn.add("UPDATE_COUNT");
		this.vecDBColumn.add("CREATOR_ID");
		this.vecDBColumn.add("CREATE_DATE");
		this.vecDBColumn.add("UPDATER_ID");
		this.vecDBColumn.add("UPDATE_DATE");
	}

	protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();

		WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(sessionContainer, dbConn);
		MtmWorkflowStepWorkflowStepDAObject stepStepDAO = new MtmWorkflowStepWorkflowStepDAObject(
				sessionContainer, dbConn);

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat
						.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.ID = ? AND A.RECORD_STATUS = ? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, id);
				this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
				rs = preStat.executeQuery();
				if (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));

					// Get the second step's allow_assign_due_date
					int count = 0;
					tmpWorkflowRecord.setNextStepDynamicAssignDueDate(GlobalConstant.FALSE);
					WorkflowStep wfStep = (WorkflowStep) stepDAO
							.getSubmitStepByWorkflowRecordID(tmpWorkflowRecord.getID());
					List mtmStepList = stepStepDAO.getListByParentStepID(wfStep.getID());
					List assignDueDateList = new ArrayList();
					for (int j = 0; j < mtmStepList.size(); j++) {
						MtmWorkflowStepWorkflowStep stepStep = (MtmWorkflowStepWorkflowStep) mtmStepList.get(j);
						WorkflowStep tmpWfStep = (WorkflowStep) stepDAO
								.getObjectByID(stepStep.getChildStepID());
						if (WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_TRUE.equals(tmpWfStep.getAllowAssignDueDate())
								|| WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_MANDATORY.equals(tmpWfStep
										.getAllowAssignDueDate())) {
							String[] currentAssignDueDateStr = new String[2];
							currentAssignDueDateStr[0] = tmpWfStep.getStepSeq() + "." + tmpWfStep.getStepName();
							currentAssignDueDateStr[1] = tmpWfStep.getAllowAssignDueDate();
							tmpWorkflowRecord.setNextStepDynamicAssignDueDate(tmpWfStep.getAllowAssignDueDate());
							count++;
							assignDueDateList.add(currentAssignDueDateStr);
						}
					}
					tmpWorkflowRecord.setNextStepDynamicAssignDueDateCount(count);
					tmpWorkflowRecord.setNextStepAllowAssignStr(assignDueDateList);

					return (tmpWorkflowRecord);
				} else {
					throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
		ListWorkflowRecordForm listSearchForm = (ListWorkflowRecordForm) searchForm;
		PreparedStatement preStat = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStat = new StringBuffer();
		StringBuffer sqlStatCnt = new StringBuffer();
		List result = new ArrayList();
		int totalNumOfRecord = 0;
		int rowLoopCnt = 0;
		int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
		int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");

				if (GlobalConstant.RECORD_PENDING.equals(listSearchForm.getStatus())) {
					sqlStat.append("AND A.STATUS = ? ");
				}
				if (!Utility.isEmpty(listSearchForm.getWorkflowGroupID())) {
					sqlStat.append("AND A.WORKFLOW_GROUP_ID = ? ");
				}

				// Form the WHERE clause for filtering.
				if (searchForm.isSearchable()) {
					String searchField = getSearchColumn(searchForm.getBasicSearchField());
					sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStat = this.getFormattedSQL(sqlStat.toString());

				// Form the ORDER clause for sorting.
				if (searchForm.isSortable()) {
					String sortAttribute = searchForm.getSortAttribute();
					if (sortAttribute.indexOf(".") < 0) {
						sortAttribute = "A." + sortAttribute;
					}
					sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
				}

				// Get total number of record return.
				sqlStatCnt = this.getSelectCountSQL(sqlStat);
				preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
				int i = 1;
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
				if (GlobalConstant.RECORD_PENDING.equals(listSearchForm.getStatus())) {
					this.setPrepareStatement(preStatCnt, i++, listSearchForm.getStatus());
				}
				if (!Utility.isEmpty(listSearchForm.getWorkflowGroupID())) {
					this.setPrepareStatement(preStatCnt, i++, listSearchForm.getWorkflowGroupID());
				}
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStatCnt, i++, searchKeyword);
				}
				rsCnt = preStatCnt.executeQuery();
				if (rsCnt.next()) {
					totalNumOfRecord = rsCnt.getInt(1);
				}
				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}

				// Retrieve the result in row basis.
				sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				i = 1;
				this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
				if (GlobalConstant.RECORD_PENDING.equals(listSearchForm.getStatus())) {
					this.setPrepareStatement(preStat, i++, listSearchForm.getStatus());
				}
				if (!Utility.isEmpty(listSearchForm.getWorkflowGroupID())) {
					this.setPrepareStatement(preStat, i++, listSearchForm.getWorkflowGroupID());
				}
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStat, i++, searchKeyword);
				}
				rs = preStat.executeQuery();

				this.positionCursor(rs, startOffset, pageSize);

				while (rs.next() && rowLoopCnt < pageSize) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					tmpWorkflowRecord.setRecordCount(totalNumOfRecord);
					tmpWorkflowRecord.setRowNum(startOffset++);
					++rowLoopCnt;
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}
			}
		}
	}

	protected synchronized List getList() throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
		//validateInsertOrUpdate(obj);//simon_del
  	//simon_add_S
    WorkflowRecord workflowRecord = (WorkflowRecord) obj;
    ApplicationExceptionList exceptionList = new ApplicationExceptionList();
    WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(sessionContainer,dbConn);

    List existRecordWithSameNameAndVersion = this.getListByWorkflowCodeWorkflowVersion(workflowRecord.getWorkflowCode(), workflowRecord.getWorkflowVersion());
    //check with the effective date
    if (!Utility.isEmpty(existRecordWithSameNameAndVersion) && existRecordWithSameNameAndVersion.size()>=1 ) {
    	exceptionList.addException(new ApplicationException(WorkflowErrorConstant.DUPLICATE_WORKFLOW_CODE_VERSION));
    }
    List existRecordWithSameName = this.getListByWorkflowCodeWorkflowGroupID(workflowRecord.getWorkflowCode(), workflowRecord.getWorkflowGroupID());
    //check with the effective date
    Timestamp newStartDate = workflowRecord.getStartDate();
    //Timestamp newEndDate = workflowRecord.getEndDate();
    if(WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(workflowRecord.getStatus())){
	    for (int i=0; i<existRecordWithSameName.size(); i++) {
	      WorkflowRecord existRecord = (WorkflowRecord) existRecordWithSameName.get(i);
	      if (existRecord.getID().equals(workflowRecord.getID())) {
	        continue;
	      }
	      Timestamp startDate = existRecord.getStartDate();
	      Timestamp endDate = existRecord.getEndDate();
	      //Timestamp currentDate=Utility.getCurrentTimestamp();
	      if(WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(existRecord.getStatus())){
	        if(endDate==null || endDate.compareTo(newStartDate)>0){
	          existRecord.setEndDate(Utility.addMinute(newStartDate,-1));
	          if(startDate.compareTo(existRecord.getEndDate())>=0){
		        	existRecord.setStartDate(Utility.addDay(existRecord.getEndDate(),-1));
		        }
	          wfRecordDAO.update(existRecord);
	        }	
	      } 
	    }
    }
    exceptionList.throwException();
    //simon_add_E*/
	}
   protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {   
    // validateInsertOrUpdate(obj);//simon_del
    // simon_add_S
    WorkflowRecord workflowRecord = (WorkflowRecord) obj;
    ApplicationExceptionList exceptionList = new ApplicationExceptionList();
    WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(sessionContainer, dbConn);
    List existRecordWithSameNameAndVersion = this.getListByWorkflowCodeWorkflowVersion(workflowRecord.getWorkflowCode(), workflowRecord.getWorkflowVersion(),workflowRecord.getID());
    // check with the effective date
    if (!Utility.isEmpty(existRecordWithSameNameAndVersion)&&existRecordWithSameNameAndVersion.size() > 0) {
      exceptionList.addException(new ApplicationException(WorkflowErrorConstant.DUPLICATE_WORKFLOW_CODE_VERSION));
    }
    List existRecordWithSameCode = this.getListByWorkflowCodeWorkflowGroupID(workflowRecord.getWorkflowCode(), workflowRecord.getWorkflowGroupID());
 
    Integer interruptCategoryID = Integer.valueOf(SystemWorkflowConstant.SUB_WORKFLOW_INTERRUPT);
    
    Timestamp currentDate = Utility.getCurrentTimestamp();
    if (WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(workflowRecord.getStatus())) {
      for (int i = 0; i < existRecordWithSameCode.size(); i++) {
        WorkflowRecord existRecord = (WorkflowRecord) existRecordWithSameCode.get(i);
        if (existRecord.getID().equals(workflowRecord.getID())) {
          continue;
        }
        Timestamp startDate = existRecord.getStartDate();
        Timestamp endDate = existRecord.getEndDate();
        if (WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(existRecord.getStatus())) {
          if (endDate == null || endDate.compareTo(currentDate) > 0) {
            existRecord.setEndDate(currentDate); // sliever.hu.update for bug[5799]
            if (startDate.compareTo(existRecord.getEndDate()) >= 0) {
              existRecord.setStartDate(Utility.addDay(existRecord.getEndDate(), -1));
            }
            wfRecordDAO.update(existRecord);
//          mod by greatwall.shao for bug[5799]
            if (interruptCategoryID.equals(workflowRecord.getWorkflowCategoryID())) {
              List parentList = this.getParentWorkflowRecordListByInterruptWorkflowRecordID(existRecord.getID());
              for (int k = 0; k < parentList.size(); k++) {
                WorkflowRecord parentWorkflow = (WorkflowRecord) parentList.get(k);
                
                  parentWorkflow.setInterruptWorkflowRecordID(workflowRecord.getID());
                  wfRecordDAO.update(parentWorkflow);
               
              }
            }//end mod by greatwall.shao for bug[5799]
          }
        }
      }
    }
    
    exceptionList.throwException();
    // simon_add_E
  }

  

  public List getParentWorkflowRecordListByInterruptWorkflowRecordID(Integer interruptWorkflowRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    ResultSet rs = null;
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat
            .append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE  ");
        sqlStat.append(" FROM WORKFLOW_RECORD  A ,SETUP_OPTION_WORKFLOW_CATEGORY B ");
        sqlStat.append(" WHERE A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? AND ");
        sqlStat.append(" A.INTERRUPT_WORKFLOW_RECORD_ID = ?  AND ? >= A.START_DATE ");
        sqlStat.append("and (A.END_DATE is null or A.END_DATE > ?)"); 
        sqlStat.append(" ORDER BY A.ID DESC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 2, interruptWorkflowRecordID);
        setPrepareStatement(preStat, 3, currTime);
        setPrepareStatement(preStat,4, currTime);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
          tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
          tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
          tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
          tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
          tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs, "INTERRUPT_WORKFLOW_RECORD_ID"));
          tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
          tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
          tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord.getCreatorID()));
          tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord.getUpdaterID()));
          result.add(tmpWorkflowRecord);
        }
        return result;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }
  }
	  
	  
	protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
	}

    protected synchronized AbstractBaseObject insert(AbstractBaseObject obj)
			throws ApplicationException {
		PreparedStatement preStat = null;
		StringBuffer sqlStat = new StringBuffer();
		WorkflowRecord tmpWorkflowRecord = (WorkflowRecord) ((WorkflowRecord) obj).clone();

		synchronized (dbConn) {
			try {
				Integer nextID = getNextPrimaryID();
				Timestamp currTime = Utility.getCurrentTimestamp();
				sqlStat.append("INSERT ");
				sqlStat
						.append("INTO   WORKFLOW_RECORD(ID, WORKFLOW_CODE, WORKFLOW_VERSION, WORKFLOW_GROUP_ID, ALLOW_INTERRUPT, INTERRUPT_WORKFLOW_RECORD_ID, STATUS, START_DATE, END_DATE, DESCRIPTION, WORKFLOW_CATEGORY_ID, DMS_PARENT_ID, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
				sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				setPrepareStatement(preStat, 1, nextID);
				setPrepareStatement(preStat, 2, tmpWorkflowRecord.getWorkflowCode());
				setPrepareStatement(preStat, 3, tmpWorkflowRecord.getWorkflowVersion());
				setPrepareStatement(preStat, 4, tmpWorkflowRecord.getWorkflowGroupID());
				setPrepareStatement(preStat, 5, tmpWorkflowRecord.getAllowInterrupt());
				setPrepareStatement(preStat, 6, tmpWorkflowRecord.getInterruptWorkflowRecordID());
				setPrepareStatement(preStat, 7, tmpWorkflowRecord.getStatus());
				setPrepareStatement(preStat, 8, tmpWorkflowRecord.getStartDate());
				setPrepareStatement(preStat, 9, tmpWorkflowRecord.getEndDate());
				setPrepareStatement(preStat, 10, tmpWorkflowRecord.getDescription());
				setPrepareStatement(preStat, 11, tmpWorkflowRecord.getWorkflowCategoryID());
				setPrepareStatement(preStat, 12, tmpWorkflowRecord.getParentID());
				setPrepareStatement(preStat, 13, GlobalConstant.RECORD_STATUS_ACTIVE);
				setPrepareStatement(preStat, 14, new Integer(0));
				setPrepareStatement(preStat, 15, sessionContainer.getUserRecordID());
				setPrepareStatement(preStat, 16, currTime);
				setPrepareStatement(preStat, 17, sessionContainer.getUserRecordID());
				setPrepareStatement(preStat, 18, currTime);
				preStat.executeUpdate();
				tmpWorkflowRecord.setID(nextID);
				tmpWorkflowRecord.setCreatorID(sessionContainer.getUserRecordID());
				tmpWorkflowRecord.setCreateDate(currTime);
				tmpWorkflowRecord.setUpdaterID(sessionContainer.getUserRecordID());
				tmpWorkflowRecord.setUpdateDate(currTime);
				tmpWorkflowRecord.setUpdateCount(new Integer(0));
				tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
						.getCreatorID()));
				tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
						.getUpdaterID()));
				return (tmpWorkflowRecord);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
			} finally {
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	protected synchronized AbstractBaseObject update(AbstractBaseObject obj)
			throws ApplicationException {
		PreparedStatement preStat = null;
		StringBuffer sqlStat = new StringBuffer();
		WorkflowRecord tmpWorkflowRecord = (WorkflowRecord) ((WorkflowRecord) obj).clone();

		synchronized (dbConn) {
			try {
				int updateCnt = 0;
				Timestamp currTime = Utility.getCurrentTimestamp();
				sqlStat.append("UPDATE WORKFLOW_RECORD ");
				sqlStat
						.append("SET  WORKFLOW_CODE=?, WORKFLOW_VERSION=?, WORKFLOW_GROUP_ID=?, ALLOW_INTERRUPT=?, INTERRUPT_WORKFLOW_RECORD_ID=?, STATUS=?, START_DATE=?, END_DATE=?, DESCRIPTION=?, WORKFLOW_CATEGORY_ID=?, DMS_PARENT_ID=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? ");
				sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				setPrepareStatement(preStat, 1, tmpWorkflowRecord.getWorkflowCode());
				setPrepareStatement(preStat, 2, tmpWorkflowRecord.getWorkflowVersion());
				setPrepareStatement(preStat, 3, tmpWorkflowRecord.getWorkflowGroupID());
				setPrepareStatement(preStat, 4, tmpWorkflowRecord.getAllowInterrupt());
				setPrepareStatement(preStat, 5, tmpWorkflowRecord.getInterruptWorkflowRecordID());
				setPrepareStatement(preStat, 6, tmpWorkflowRecord.getStatus());
				setPrepareStatement(preStat, 7, tmpWorkflowRecord.getStartDate());
				setPrepareStatement(preStat, 8, tmpWorkflowRecord.getEndDate());
				setPrepareStatement(preStat, 9, tmpWorkflowRecord.getDescription());
				setPrepareStatement(preStat, 10, tmpWorkflowRecord.getWorkflowCategoryID());
				setPrepareStatement(preStat, 11, tmpWorkflowRecord.getParentID());
				setPrepareStatement(preStat, 12, new Integer(
						tmpWorkflowRecord.getUpdateCount().intValue() + 1));
				setPrepareStatement(preStat, 13, sessionContainer.getUserRecordID());
				setPrepareStatement(preStat, 14, currTime);
				setPrepareStatement(preStat, 15, tmpWorkflowRecord.getID());
				setPrepareStatement(preStat, 16, tmpWorkflowRecord.getUpdateCount());
				updateCnt = preStat.executeUpdate();
				if (updateCnt == 0) {
					throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
				} else {
					tmpWorkflowRecord.setUpdaterID(sessionContainer.getUserRecordID());
					tmpWorkflowRecord.setUpdateDate(currTime);
					tmpWorkflowRecord.setUpdateCount(new Integer(tmpWorkflowRecord.getUpdateCount()
							.intValue() + 1));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					return (tmpWorkflowRecord);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
			} finally {
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	protected synchronized AbstractBaseObject delete(AbstractBaseObject obj)
			throws ApplicationException {
		PreparedStatement preStat = null;
		StringBuffer sqlStat = new StringBuffer();
		WorkflowRecord tmpWorkflowRecord = (WorkflowRecord) ((WorkflowRecord) obj).clone();

		synchronized (dbConn) {
			try {
				int updateCnt = 0;
				sqlStat.append("DELETE ");
				sqlStat.append("FROM   WORKFLOW_RECORD ");
				sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				setPrepareStatement(preStat, 1, tmpWorkflowRecord.getID());
				setPrepareStatement(preStat, 2, tmpWorkflowRecord.getUpdateCount());
				updateCnt = preStat.executeUpdate();
				if (updateCnt == 0) {
					throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
				} else {
					return (tmpWorkflowRecord);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
			} finally {
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	protected synchronized void auditTrail(String opMode, AbstractBaseObject obj)
			throws ApplicationException {
		Vector oldValues = new Vector();
		Vector newValues = new Vector();
		WorkflowRecord tmpWorkflowRecord = (WorkflowRecord) this.oldValue;

		if (tmpWorkflowRecord != null) {
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowCode()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowVersion()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowGroupID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getAllowInterrupt()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getInterruptWorkflowRecordID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getStatus()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getStartDate()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getEndDate()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getDescription()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowCategoryID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getParentID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getRecordStatus()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdateCount()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getCreatorID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getCreateDate()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdaterID()));
			oldValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdateDate()));
		}

		tmpWorkflowRecord = (WorkflowRecord) obj;
		if (tmpWorkflowRecord != null) {
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowCode()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowVersion()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowGroupID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getAllowInterrupt()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getInterruptWorkflowRecordID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getStatus()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getStartDate()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getEndDate()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getDescription()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getWorkflowCategoryID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getParentID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getRecordStatus()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdateCount()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getCreatorID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getCreateDate()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdaterID()));
			newValues.add(toAuditTrailValue(tmpWorkflowRecord.getUpdateDate()));
		}

		auditTrailBase(opMode, oldValues, newValues);
	}

	/*****************************************************************************
	 * DON'T Modify the codes above unless you know what you are doing!!! * Put
	 * your own functions beblow. * For FINDER methods, the function name should
	 * be in the notation: * public Object getObjectBy<Criteria>() * - e.g.
	 * public Object getObjectByCode() * public List getListBy<Criteria>() * -
	 * e.g. public List getListByUserID() * For OPERATION methods, the function
	 * name should be in the notation: * public void <Operation>ObjectBy<Criteria>() * -
	 * e.g. public void deleteObjectByCode() * public void <Operation>ListBy<Criteria>() * -
	 * e.g. public void deleteListByUserID() *
	 ****************************************************************************/
	/**
	 * Gets the workflow record object by a given workflow track ID
	 * 
	 * @param trackID
	 *          The workflow track ID
	 * @return The matched WorkflowRecord if any
	 * @throws ApplicationException
	 * 
	 * <pre>
	 *   
	 *              ApplicationException with the following message codes will be thrown
	 *              ErrorConstant.DB_RECORD_NOT_FOUND_ERROR - No matching record found
	 *              ErrorConstant.ErrorConstant.DB_GENERAL_ERROR - General caught SQLException
	 *              ErrorConstant.DB_SELECT_ERROR - All kind of caught Exception
	 *    
	 * </pre>
	 * 
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized AbstractBaseObject getObjectByTrackID(Integer trackID)
			throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.DMS_PARENT_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat
						.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B, WORKFLOW_PROGRESS C, WORKFLOW_STEP D ");
				sqlStat
						.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND C.WORKFLOW_STEP_ID = D.ID AND D.WORKFLOW_RECORD_ID = A.ID AND C.SEQ_NO = 1 AND C.TRACK_ID = ? AND A.RECORD_STATUS = ? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, trackID);
				this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
				rs = preStat.executeQuery();
				if (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					return (tmpWorkflowRecord);
				} else {
					throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets the last updated workflow record object of the given workflow group
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @return The last updated workflow record
	 * @throws ApplicationException
	 * 
	 * <pre>
	 *   
	 *              ApplicationException with the following message codes will be thrown
	 *              ErrorConstant.DB_RECORD_NOT_FOUND_ERROR - No matching record found
	 *              ErrorConstant.ErrorConstant.DB_GENERAL_ERROR - General caught SQLException
	 *              ErrorConstant.DB_SELECT_ERROR - All kind of caught Exception
	 *    
	 * </pre>
	 * 
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized AbstractBaseObject getLastUpdateObjectByWorkflowGroupID(
			Integer workflowGroupID) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append("FROM   WORKFLOW_RECORD A ");
				sqlStat.append("WHERE  A.WORKFLOW_GROUP_ID = ? AND A.RECORD_STATUS = ? ");
				sqlStat.append("ORDER BY UPDATE_DATE DESC ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, workflowGroupID);
				this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
				rs = preStat.executeQuery();
				if (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					return (tmpWorkflowRecord);
				} else {
					throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}
	/**
	 * Gets a list of workflow record that is belong to the given system category
	 * 
	 * @param sysWorkflowCategoryID
	 *          The system category ID
	 * @return List of matched workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getLastBySysWorkflowCategoryID(Integer sysWorkflowCategoryID)
			throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();
		Timestamp currTime = Utility.getCurrentTimestamp();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append("FROM   WORKFLOW_RECORD A ");
				sqlStat
						.append("WHERE  A.WORKFLOW_CATEGORY_ID = ? AND A.STATUS = ? AND A.RECORD_STATUS = ? ");
				sqlStat.append(" AND ? >= A.START_DATE AND ( A.END_DATE  >= ?  OR A.END_DATE IS NULL) ");
				sqlStat.append("ORDER BY UPDATE_DATE DESC ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, sysWorkflowCategoryID);
				this.setPrepareStatement(preStat, 2, WorkflowRecord.WORKFLOW_RECORD_RELEASE);
				this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 4, currTime);
	            this.setPrepareStatement(preStat, 5, currTime);             //sliever.hu.update
				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets the number of active workflow record under the given workflow group
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @return The total number of active workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 */
	public synchronized Integer getNoOfActiveRecordByWorkflowGroupID(Integer workflowGroupID)
			throws ApplicationException {
		return this.getNoOfActiveRecordByWorkflowGroupIDWorkflowCategoryID(workflowGroupID, null);
	}
  
  /**
   * <p>Get the list of workflow's id(s) that current user can read.</p>
   * 
   * <p>
   * Relates with {@link WorkflowProgressDAObject.getPendingProgressIDs2Display4CurrentUser(Integer)}
   * and {@link WorkflowPermissionManager.getUserPermission4SpecialTask(Integer, Integer, Integer)}.
   * 
   * NOTE:<code>WorkflowRecordDAObject</code> ignore special dynamic user such as 'k' 'V' etc.
   * </p>
   * 
   * <pre>
   * Permission priority hierarchy
   * 
   * 1. User
   * 2. Roles with deny permission (roleA and roleB etc)
   * 3. Roles with allow permission (roleC and roleD etc)
   * 4. Groups with deny permission (groupA and groupB etc)
   * 5. Groups with allow permission (groupC and groupD etc)
   * 6. Every one
   * 
   * In the condition there are repeat definition in same priority level, 'DENY' permission is priority.
   * </pre>
   * 
   * <p>
   * <b>Permission Inheritance</b>
   * 
   * <p>
   * The inherited permission for current account, is equal to the first
   * non-null permission in the object hierarchy, starting at user and
   * proceeding upwards in the hierarchy towards the root permission. 
   * Below are four tables with various assigned permission values and the
   * resulting inherited permission according to the above rule.
   * <p>
   * Current account is User A and Role A and Role B and Group A.
   * 
   * <p>Example 1</p>
   * 
   * <table border=1>
   * <tr><td>Object              </td><td>Assigned Permission of Workflow Record A</td></tr>
   * <tr><td>Every one           </td><td>Allow                                        </td></tr>
   * <tr><td>Group A             </td><td>none                                         </td></tr>
   * <tr><td>Role A              </td><td>none                                         </td></tr>
   * <tr><td>User A              </td><td>none                                         </td></tr>
   * <tr><td>Inherited Permission</td><td>Allow                                        </td></tr>
   * </table>
   * 
   * <p>
   * In example 1 above, only the root object is assigned a permission. This permission 
   * value, of <code>User A</code>, inherite from the root object <code>Every one</code>.
   * 
   * <p>Example 2</p>
   * <table border=1>
   * <tr><td>Object              </td><td>Assigned Permission of Workflow Record A</td></tr>
   * <tr><td>Every one           </td><td>Allow                                        </td></tr>
   * <tr><td>Group A             </td><td>none                                         </td></tr>
   * <tr><td>Role A              </td><td>none                                         </td></tr>
   * <tr><td>User A              </td><td>Deny                                         </td></tr>
   * <tr><td>Inherited Permission</td><td>Deny                                         </td></tr>
   * </table>
   * 
   * <p>
   * In example 2, <code>User A</code> have an assigned permission value.
   * There is no need for permission inheritence.
   * 
   * <p>Example 3</p>
   * <table border=1>
   * <tr><td>Object              </td><td>Assigned Permission of Workflow Record A</td></tr>
   * <tr><td>Every one           </td><td>Allow                                        </td></tr>
   * <tr><td>Group A             </td><td>none                                         </td></tr>
   * <tr><td>Role A              </td><td>Allow                                        </td></tr>
   * <tr><td>User A              </td><td>none                                         </td></tr>
   * <tr><td>Inherited Permission</td><td>Allow                                        </td></tr>
   * </table>
   * 
   * <p>
   * In example 3, the <code>User A</code>'s permission is none,
   * but <code>Role A</code> have an assigned permission value,
   * so inherite permission value from <code>Role A</code>. 
   * 
   * <p>Example 4</p>
   * <table border=1>
   * <tr><td>Object              </td><td>Assigned Permission of Workflow Record A</td></tr>
   * <tr><td>Every one           </td><td>Allow                                        </td></tr>
   * <tr><td>Group A             </td><td>none                                         </td></tr>
   * <tr><td>Role A              </td><td>Allow                                        </td></tr>
   * <tr><td>Role B              </td><td>Deny                                         </td></tr>
   * <tr><td>User A              </td><td>none                                         </td></tr>
   * <tr><td>Inherited Permission</td><td>Deny                                         </td></tr>
   * </table>
   * 
   * <p>
   * In example 4, the <code>User A</code>'s permission is none,
   * but <code>Role A</code> and <code>Role B</code> all have an assigned permission value,
   * 'DENY' permission is priority, so inherite permission from <code>Role B</code>.
   * 
   * @return a text of string contains workflow's id(s) that current user can read,
   *         return null if the list can read is empty.
   * @throws ApplicationException
   */
  private String getWorkflowIDs2Display4CurrentUser() throws ApplicationException {
    
    StringBuffer workflowIDs4CurrentUser = new StringBuffer();
    
    Set workflowRecordIDsSet4User = new HashSet();
    Set workflowRecordIDsSet4UserRead = new HashSet();
    Set workflowRecordIDsSet4UserDeny = new HashSet();
    
    Set workflowRecordIDsSet4Roles = new HashSet();
    Set workflowRecordIDsSet4RolesRead = new HashSet();
    Set workflowRecordIDsSet4RolesDeny = new HashSet();
    
    Set workflowRecordIDsSet4Groups = new HashSet();
    Set workflowRecordIDsSet4GroupsRead = new HashSet();
    Set workflowRecordIDsSet4GroupsDeny = new HashSet();
    
    Set workflowRecordIDsSet4Everyone = new HashSet();
    Set workflowRecordIDsSet4EveryoneRead = new HashSet();
    Set workflowRecordIDsSet4EveryoneDeny = new HashSet();
    
    Integer userRecordID = sessionContainer.getUserRecordID();
    
    // Construct the roles and groups of current user.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    StringBuffer userRolesIDs = new StringBuffer();
    StringBuffer userGroupsIDs = new StringBuffer();
    
    PermissionManager permissionManager = sessionContainer.getPermissionManager();
    List userRolesIDList = permissionManager.getUserRoles();
    List userGroupsIDList = permissionManager.getUserGroups();
    
    if (!Utility.isEmpty(userRolesIDList)) {
      for (int r = 0; r < userRolesIDList.size(); r++) {
        if (r != 0) {
          userRolesIDs.append(", ");
        }
        userRolesIDs.append(((UserRole) userRolesIDList.get(r)).getID());
      }
    }
    
    if (!Utility.isEmpty(userGroupsIDList)) {
      for (int g = 0; g < userGroupsIDList.size(); g++) {
        if (g != 0) {
          userGroupsIDs.append(", ");
        }
        userGroupsIDs.append(((UserGroup) userGroupsIDList.get(g)).getID());
      }
    }
    
    // Get the whole list of 'read' permission relates current user.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PreparedStatement readState = null;
    ResultSet readRS = null;
    
    StringBuffer readQuery = new StringBuffer();
    readQuery.append("SELECT  DISTINCT WR.ID, MWSUA.PERMISSION, MWSUA.ACTOR_TYPE, MWSUA.ACTOR_ID \n");
    readQuery.append("FROM    WORKFLOW_STEP WS, WORKFLOW_RECORD WR, MTM_WF_STEP_USER_ACTOR MWSUA ,SETUP_OPTION_WORKFLOW_CATEGORY SOWC \n");
    readQuery.append("WHERE   WS.ACTION_TYPE = ? AND \n");
    readQuery.append("        MWSUA.ACTION_TYPE = ? AND \n");
    readQuery.append("        WR.ID = WS.WORKFLOW_RECORD_ID AND \n");
    readQuery.append("        WR.WORKFLOW_CATEGORY_ID = SOWC.ID AND \n");
    readQuery.append("        WR.RECORD_STATUS = ? AND \n");
    readQuery.append("        WR.STATUS = ? AND \n");
    readQuery.append("        WS.ID = MWSUA.WORKFLOW_STEP_ID AND \n");
    readQuery.append("        ? >= WR.START_DATE AND \n");
    readQuery.append("        (? < WR.END_DATE OR WR.END_DATE IS NULL) AND \n");
    readQuery.append("        (MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ? OR MWSUA.ACTOR_TYPE = ? \n");
    
    if (!Utility.isEmpty(userRolesIDs)) {
      readQuery.append("         OR MWSUA.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND MWSUA.ACTOR_ID IN ( " + userRolesIDs + " ) \n");
    }
    
    if (!Utility.isEmpty(userGroupsIDs)) {
      readQuery.append("         OR MWSUA.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND MWSUA.ACTOR_ID IN ( " + userGroupsIDs + " ) \n");
    }
    
    readQuery.append("        )");
    
    // Format the sql for any 'LIKE' statement contained
    readQuery = this.getFormattedSQL(readQuery.toString());

    try {
      readState = dbConn.prepareStatement(readQuery.toString());
      
      Timestamp currTime = Utility.getCurrentTimestamp();
      Timestamp tmrTime = new Timestamp(currTime.getTime());
      
      this.setPrepareStatement(readState, 1, WorkflowStep.ACTION_TYPE_SUBMIT);
      this.setPrepareStatement(readState, 2, MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
      this.setPrepareStatement(readState, 3, GlobalConstant.STATUS_ACTIVE);
      this.setPrepareStatement(readState, 4, GlobalConstant.RECORD_RELEASED);
      this.setPrepareStatement(readState, 5, currTime);
      this.setPrepareStatement(readState, 6, tmrTime);
      this.setPrepareStatement(readState, 7, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
      this.setPrepareStatement(readState, 8, userRecordID);
      this.setPrepareStatement(readState, 9, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);
      
      readRS = readState.executeQuery();
      
      // Parse the permission definition.
      // Permission priotity hierarchy
      // 
      // 1. User
      // 2. Roles with deny permission (roleA and roleB etc)
      // 3. Roles with allow permission (roleC and roleD etc)
      // 4. Groups with deny permission (groupA and groupB etc)
      // 5. Groups with allow permission (groupC and groupD etc)
      // 6. Every one
      // 
      // In the condition there are repeat definition in same priority level, 'DENY' permission is priority.
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      while (!Utility.isEmpty(readRS) && readRS.next()) {
        Integer recordID = this.getResultSetInteger(readRS, "ID");
        String actorType = this.getResultSetString(readRS, "ACTOR_TYPE");
        String permission = this.getResultSetString(readRS, "PERMISSION");
        
        if (recordID == null || actorType == null || permission == null) {
          continue;
        }
        
        // Exits parsing if permission about 'read' is empty.
        if (permission.indexOf(PermissionManager.DENIED_READ_PERMISSION) < 0 && permission.indexOf(PermissionManager.READ_PERMISSION) < 0) {
          continue;
        }
        
        // Type of user.
        // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        if (MtmWorkflowStepUserActor.ACTOR_TYPE_USER.equals(actorType)) {
          
          // If type of user have been in deny list, it means that no need to check current user have read permission with current record or not.
          if (workflowRecordIDsSet4UserDeny.contains(recordID)) {
            continue;
          }
          
          // Type of user, remove the definition from roles and groups if exists.
          workflowRecordIDsSet4EveryoneRead.remove(recordID);
          workflowRecordIDsSet4GroupsRead.remove(recordID);
          workflowRecordIDsSet4RolesRead.remove(recordID);
          
          // Record into user definition.
          workflowRecordIDsSet4User.add(recordID);

          if (permission.indexOf(PermissionManager.READ_PERMISSION) > -1) {
            workflowRecordIDsSet4UserRead.add(recordID);
          }
          
          // In the condition there are repeat definition in same priority level, 'DENY' permission is priority.
          if (permission.indexOf(PermissionManager.DENIED_READ_PERMISSION) > -1) {
            workflowRecordIDsSet4UserRead.remove(recordID);
            workflowRecordIDsSet4UserDeny.add(recordID);
          }
        }
        
        // Type of role.
        // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        if (MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE.equals(actorType)) {
          
          // If the permission have been definition with user, exit to next.
          if (workflowRecordIDsSet4User.contains(recordID)) {
            continue;
          }
          
          if (workflowRecordIDsSet4RolesDeny.contains(recordID)) {
            continue;
          }
          
          workflowRecordIDsSet4EveryoneRead.remove(recordID);
          workflowRecordIDsSet4GroupsRead.remove(recordID);
          
          workflowRecordIDsSet4Roles.add(recordID);
          if (permission.indexOf(PermissionManager.READ_PERMISSION) > -1) {
            workflowRecordIDsSet4RolesRead.add(recordID);
          }
          
          if (permission.indexOf(PermissionManager.DENIED_READ_PERMISSION) > -1) {
            workflowRecordIDsSet4RolesRead.remove(recordID);
            workflowRecordIDsSet4RolesDeny.add(recordID);
          }
        }
        
        // Type of group.
        // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        if (MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP.equals(actorType)) {
          if (workflowRecordIDsSet4User.contains(recordID) || workflowRecordIDsSet4Roles.contains(recordID)) {
            continue;
          }
          
          if (workflowRecordIDsSet4GroupsDeny.contains(recordID)) {
            continue;
          }
          
          workflowRecordIDsSet4EveryoneRead.remove(recordID);
          
          workflowRecordIDsSet4Groups.add(recordID);
          if (permission.indexOf(PermissionManager.READ_PERMISSION) > -1) {
            workflowRecordIDsSet4GroupsRead.add(recordID);
          }
          
          if (permission.indexOf(PermissionManager.DENIED_READ_PERMISSION) > -1) {
            workflowRecordIDsSet4GroupsRead.remove(recordID);
            workflowRecordIDsSet4GroupsDeny.add(recordID);
          }
        }
        
        // Type of every one.
        // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        if (MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE.equals(actorType)) {
          if (workflowRecordIDsSet4User.contains(recordID)
              || workflowRecordIDsSet4Roles.contains(recordID)
              || workflowRecordIDsSet4Groups.contains(recordID)) {
            continue;
          }
          
          if (workflowRecordIDsSet4EveryoneDeny.contains(recordID)) {
            continue;
          }
          
          workflowRecordIDsSet4Everyone.add(recordID);
          if (permission.indexOf(PermissionManager.READ_PERMISSION) > -1) {
            workflowRecordIDsSet4EveryoneRead.add(recordID);
          }
          
          if (permission.indexOf(PermissionManager.DENIED_READ_PERMISSION) > -1) {
            workflowRecordIDsSet4EveryoneRead.remove(recordID);
            workflowRecordIDsSet4EveryoneDeny.add(recordID);
          }
        }
      }
      
      // Pack up the workflow record id(s) that current user have permission to read.
      // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      for (Iterator iter = workflowRecordIDsSet4UserRead.iterator(); iter.hasNext();) {
        workflowIDs4CurrentUser.append(iter.next() + ",");
      }
      
      for (Iterator iter = workflowRecordIDsSet4RolesRead.iterator(); iter.hasNext();) {
        workflowIDs4CurrentUser.append(iter.next() + ",");
      }
      
      for (Iterator iter = workflowRecordIDsSet4GroupsRead.iterator(); iter.hasNext();) {
        workflowIDs4CurrentUser.append(iter.next() + ",");
      }
      
      for (Iterator iter = workflowRecordIDsSet4EveryoneRead.iterator(); iter.hasNext();) {
        workflowIDs4CurrentUser.append(iter.next() + ",");
      }
      
      if (workflowIDs4CurrentUser.length() > 0) {
        return (workflowIDs4CurrentUser.substring(0, workflowIDs4CurrentUser.length() - 1));
      }
      
      return (null);
      
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try {
        readRS.close();
        readState.close();
      } catch (Exception ignore) {
        // Ignore.
      }
    }
  }

	/**
	 * Gets a list of workflow record that the current user can submit. The user
	 * record is pre-set in the SessionContainer object during the creation of the
	 * DAObject,
	 * 
	 * <pre>
	 * sessionContainer.setUserRecord(userRecord);
	 * </pre>
	 * 
	 * Basic search can be achieved by,
	 * 
	 * <pre>
	 * searchForm.setBasicSearchField(&quot;WORKFLOW_RECORD&quot;);
	 * searchForm.setBasicSearchType(&quot;=&quot;);
	 * searchForm.setBasicSearchKeyword(&quot;WF001&quot;);
	 * </pre>
	 * 
	 * @param searchForm
	 *          The abstract search form object
	 * @return List of matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.web.ListWorkflowRecordForm
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */ 
	public synchronized List getListCanSubmit(AbstractSearchForm searchForm)
			throws ApplicationException {
		ListWorkflowRecordForm listSearchForm = (ListWorkflowRecordForm) searchForm;
		PermissionManager pm = sessionContainer.getPermissionManager();
		Integer userRecordID = sessionContainer.getUserRecordID();
		FormRecordDAObject formRecordDAO = new FormRecordDAObject(this.sessionContainer, this.dbConn);
    DmsDocumentDAObject dmsDAO = new DmsDocumentDAObject(this.sessionContainer, this.dbConn);
    SysUserDefinedIndexDAObject profileDAO = new SysUserDefinedIndexDAObject(this.sessionContainer, this.dbConn);
    
		List userRolesID = pm.getUserRoles();
		List userGroupsID = pm.getUserGroups();
		Integer workflowCategoryID = TextUtility.parseIntegerObj(listSearchForm.getWorkflowCategoryID());
		PreparedStatement preStat = null;
		PreparedStatement preStatCnt = null;
		ResultSet rs = null;
		ResultSet rsCnt = null;
		StringBuffer sqlStat = new StringBuffer();
		StringBuffer sqlStatCnt = new StringBuffer();
		List result = new ArrayList();
		int totalNumOfRecord = 0;
		int rowLoopCnt = 0;
		int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
		int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
		String userRoleIDs = "";
		String userGroupIDs = "";
		List permissionWfRecordIDList = new ArrayList();
		List noPermissionWfRecordIDList = new ArrayList();
		String permissionWfRecordIDs = "";
    
    WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(sessionContainer, dbConn);
    MtmWorkflowStepWorkflowStepDAObject stepStepDAO = new MtmWorkflowStepWorkflowStepDAObject(sessionContainer, dbConn);
        
		synchronized (dbConn) {
			try {
        
        int i = 1;
        /* Extract to method getWorkflowIDList2Display4CurrentUser().
				// get the userGroupIDs
				for (int i = 0; userGroupsID != null && i < userGroupsID.size(); i++) {
					if (i != 0) {
						userGroupIDs += ", ";
					}
					UserGroup group = (UserGroup) userGroupsID.get(i);
					userGroupIDs += group.getID();
				}

				// get the userRoleIDs
				for (int i = 0; userRolesID != null && i < userRolesID.size(); i++) {
					if (i != 0) {
						userRoleIDs += ", ";
					}
					UserRole role = (UserRole) userRolesID.get(i);
					userRoleIDs += role.getID();
				}

				// get possible Workflow RecordID
				sqlStat.append("SELECT  DISTINCT WR.ID, MWSUA.PERMISSION, MWSUA.ACTOR_TYPE, MWSUA.ACTOR_ID \n");
				sqlStat.append("FROM    WORKFLOW_STEP WS, WORKFLOW_RECORD WR, MTM_WF_STEP_USER_ACTOR MWSUA ,SETUP_OPTION_WORKFLOW_CATEGORY SOWC \n");
				sqlStat.append("WHERE   WS.ACTION_TYPE = ? AND \n");
				sqlStat.append("        MWSUA.ACTION_TYPE = ? AND \n");
				sqlStat.append("        WR.ID = WS.WORKFLOW_RECORD_ID AND \n");
				sqlStat.append("        WR.WORKFLOW_CATEGORY_ID = SOWC.ID AND \n");
				sqlStat.append("        WR.RECORD_STATUS = ? AND \n");
				sqlStat.append("        WR.STATUS = ? AND \n");
				sqlStat.append("        WS.ID = MWSUA.WORKFLOW_STEP_ID AND \n");
				sqlStat.append("        ? >= WR.START_DATE AND \n");
				sqlStat.append("        (? < WR.END_DATE OR WR.END_DATE IS NULL) AND \n");
				sqlStat.append("        (MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ? OR MWSUA.ACTOR_TYPE = ? \n");

				if (!"".equals(userGroupIDs)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"	+ MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND MWSUA.ACTOR_ID IN ("	+ userGroupIDs + ") \n");
				}

				if (!"".equals(userRoleIDs)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"	+ MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND MWSUA.ACTOR_ID IN (" + userRoleIDs + ") \n");
				}
				sqlStat.append("        ) ");

				// Form the WHERE clause for filtering.
				if (searchForm.isSearchable()) {
					String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
					sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStat = this.getFormattedSQL(sqlStat.toString());

				// Get total number of record return.
				preStat = dbConn.prepareStatement(sqlStat.toString());
				Timestamp currTime = Utility.getCurrentTimestamp();
				Timestamp tmrTime = new Timestamp(currTime.getTime());
				log.debug(sqlStat);
				log.debug(WorkflowStep.ACTION_TYPE_SUBMIT);
				log.debug(MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				log.debug(GlobalConstant.STATUS_ACTIVE);
				log.debug(GlobalConstant.RECORD_RELEASED);
				log.debug(currTime);
				log.debug(tmrTime);
				log.debug(MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				log.debug(userRecordID);
				this.setPrepareStatement(preStat, 1, WorkflowStep.ACTION_TYPE_SUBMIT);
				this.setPrepareStatement(preStat, 2, MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_RELEASED);
				this.setPrepareStatement(preStat, 5, currTime);
				this.setPrepareStatement(preStat, 6, tmrTime);
				this.setPrepareStatement(preStat, 7, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				this.setPrepareStatement(preStat, 8, userRecordID);
				this.setPrepareStatement(preStat, 9, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);
				int i = 9;
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStat, 10, searchKeyword);
				}
				rs = preStat.executeQuery();
				String possibleWorkflowRecordIDs = "";
				while (rs.next()) {
					String permission = getResultSetString(rs, "PERMISSION");
          
          if (!this.checkBindingFormExpried(getResultSetInteger(rs, "ID"))) {				
						if (permission.indexOf("w") < 0) {
							if (permissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) < 0
									&& noPermissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) < 0) {
								permissionWfRecordIDList.add(getResultSetInteger(rs, "ID"));
							}
						} else {
							noPermissionWfRecordIDList.add(getResultSetInteger(rs, "ID"));
							if (permissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) > -1) {
								permissionWfRecordIDList.remove(getResultSetInteger(rs, "ID"));
							}
						}
          }
				}

				for (int j = 0; permissionWfRecordIDList != null && j < permissionWfRecordIDList.size(); j++) {
					if (j != 0) {
						possibleWorkflowRecordIDs += ", ";
					}
					possibleWorkflowRecordIDs += permissionWfRecordIDList.get(j).toString();
				}

				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
        */
        String possibleWorkflowRecordIDs = this.getWorkflowIDs2Display4CurrentUser();
        
				if (Utility.isEmpty(possibleWorkflowRecordIDs)) {
					return result;
				}

				// * base on the workflow record list got from above further
				// select workflow record
				sqlStat = new StringBuffer();
				StringBuffer sqlStatTemp = new StringBuffer();

				// * Form Related
				sqlStatTemp = new StringBuffer();
				sqlStatTemp.append("SELECT  DISTINCT WR.ID, WR.WORKFLOW_CODE, WR.WORKFLOW_VERSION, WR.WORKFLOW_GROUP_ID, WR.ALLOW_INTERRUPT, WR.INTERRUPT_WORKFLOW_RECORD_ID, WR.STATUS, WR.START_DATE, WR.END_DATE, WR.DESCRIPTION, WR.WORKFLOW_CATEGORY_ID, WR.DMS_PARENT_ID, WR.RECORD_STATUS, WR.UPDATE_COUNT, WR.CREATOR_ID, WR.CREATE_DATE, WR.UPDATER_ID, WR.UPDATE_DATE, SOWC.WORKFLOW_CATEGORY_NAME, -1 as RELATED_OBJECT_ID, '"	+ GlobalConstant.OBJECT_TYPE_FORM	+ "' as RELATED_OBJECT_TYPE, WR.WORKFLOW_CODE as RELATED_OBJECT_NAME \n");
				sqlStatTemp.append("FROM    WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_RECORD_OBJECT_LINK MWROL, FORM_RECORD FR \n");
				sqlStatTemp.append("WHERE   WR.WORKFLOW_CATEGORY_ID = SOWC.ID \n");
				sqlStatTemp.append("        AND SOWC.ID = ? \n");
				sqlStatTemp.append("        AND WR.ID IN (" + possibleWorkflowRecordIDs + ") \n");
				sqlStatTemp.append("        AND MWROL.WORKFLOW_RECORD_ID = WR.ID \n");
				sqlStatTemp.append("        AND MWROL.OBJECT_TYPE = ? \n"); // GlobalConstant.OBJECT_TYPE_FORM
				sqlStatTemp.append("        AND MWROL.OBJECT_ID = FR.ID \n");
				sqlStatTemp.append("        AND FR.STATUS = ? \n");
        sqlStatTemp.append("        AND ( WR.END_DATE >= ? OR WR.END_DATE IS NULL) \n");
        sqlStatTemp.append("        AND ( FR.END_DATE >= ? OR FR.END_DATE IS NULL) \n");
        
				// Form the WHERE clause for filtering.
				if (searchForm.isSearchable()) {
					String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
					sqlStatTemp.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStatTemp = this.getFormattedSQL(sqlStatTemp.toString());

				// Get total number of record return.
				sqlStat = sqlStat.append(sqlStatTemp);
				sqlStat.append("UNION \n");
				// sqlStatCnt = this.getSelectCountSQL(sqlStatTemp);
				sqlStatCnt = sqlStatTemp;
				preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
				i = 1;
				this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.FMB_SUBMISSION_BY_FORM);
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.OBJECT_TYPE_FORM);
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStatCnt, i++, searchKeyword);
				}
				rsCnt = preStatCnt.executeQuery();
				while (rsCnt.next()) {
					// totalNumOfRecord += rsCnt.getInt(1);
					totalNumOfRecord++;// because of the select DISTINCT
				}

				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}

				// * Form Group Related
				sqlStatTemp = new StringBuffer();
				sqlStatTemp
						.append("SELECT  DISTINCT WR.ID, WR.WORKFLOW_CODE, WR.WORKFLOW_VERSION, WR.WORKFLOW_GROUP_ID, WR.ALLOW_INTERRUPT, WR.INTERRUPT_WORKFLOW_RECORD_ID, WR.STATUS, WR.START_DATE, WR.END_DATE, WR.DESCRIPTION, WR.WORKFLOW_CATEGORY_ID, WR.DMS_PARENT_ID, WR.RECORD_STATUS, WR.UPDATE_COUNT, WR.CREATOR_ID, WR.CREATE_DATE, WR.UPDATER_ID, WR.UPDATE_DATE, SOWC.WORKFLOW_CATEGORY_NAME, FR.ID as RELATED_OBJECT_ID, '"
								+ GlobalConstant.OBJECT_TYPE_FORMGROUP
								+ "' as RELATED_OBJECT_TYPE, "
								+ DataSourceFactory.formatSQLConcatStatement(new String[] { "FG.GROUP_NAME",
										"': '", "FR.FORM_CODE" }, "") + " as RELATED_OBJECT_NAME \n");
				sqlStatTemp
						.append("FROM    WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_RECORD_OBJECT_LINK MWROL, FORM_RECORD FR, FORM_GROUP FG \n");
				sqlStatTemp.append("WHERE   WR.WORKFLOW_CATEGORY_ID = SOWC.ID \n");
				sqlStatTemp.append("        AND SOWC.ID = ? \n");
				sqlStatTemp.append("        AND WR.ID IN (" + possibleWorkflowRecordIDs + ") \n");
				sqlStatTemp.append("        AND MWROL.WORKFLOW_RECORD_ID = WR.ID \n");
				sqlStatTemp.append("        AND MWROL.OBJECT_TYPE = ? \n"); // GlobalConstant.OBJECT_TYPE_FORMGROUP
				sqlStatTemp.append("        AND MWROL.OBJECT_ID = FG.ID \n");
				sqlStatTemp.append("        AND FR.FORM_GROUP_ID = FG.ID \n");
				sqlStatTemp.append("        AND FR.STATUS = ? \n");

				// Form the WHERE clause for filtering.
				if (searchForm.isSearchable()) {
					String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
					sqlStatTemp.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStatTemp = this.getFormattedSQL(sqlStatTemp.toString());

				// Get total number of record return.
				sqlStat = sqlStat.append(sqlStatTemp);
				sqlStat.append("UNION \n");
				sqlStatCnt = this.getSelectCountSQL(sqlStatTemp);
				preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
				i = 1;
				this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.FMB_SUBMISSION_BY_GROUP);
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.OBJECT_TYPE_FORMGROUP);
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_RELEASED);
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStatCnt, i++, searchKeyword);
				}
				rsCnt = preStatCnt.executeQuery();
				if (rsCnt.next()) {
					totalNumOfRecord += rsCnt.getInt(1);
				}
				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}

        /*****************************************************************************************************
        *                             Public Folder Workflow Begin
        ******************************************************************************************************/
        //* Public Folder Related
        sqlStatTemp = new StringBuffer();
        sqlStatTemp.append("SELECT  DISTINCT WR.ID, WR.WORKFLOW_CODE, WR.WORKFLOW_VERSION, WR.WORKFLOW_GROUP_ID, WR.ALLOW_INTERRUPT, WR.INTERRUPT_WORKFLOW_RECORD_ID, WR.STATUS, WR.START_DATE, WR.END_DATE, WR.DESCRIPTION, WR.WORKFLOW_CATEGORY_ID, WR.DMS_PARENT_ID, WR.RECORD_STATUS, WR.UPDATE_COUNT, WR.CREATOR_ID, WR.CREATE_DATE, WR.UPDATER_ID, WR.UPDATE_DATE, SOWC.WORKFLOW_CATEGORY_NAME, -1 as RELATED_OBJECT_ID, '" + GlobalConstant.OBJECT_TYPE_DOCUMENT + "' as RELATED_OBJECT_TYPE, WR.WORKFLOW_CODE as RELATED_OBJECT_NAME \n");
        sqlStatTemp.append("FROM    WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_RECORD_OBJECT_LINK MWROL, DMS_DOCUMENT DOC \n");
        sqlStatTemp.append("WHERE   WR.WORKFLOW_CATEGORY_ID = SOWC.ID \n");        
        sqlStatTemp.append("        AND WR.ID IN (" + possibleWorkflowRecordIDs + ") \n");
        sqlStatTemp.append("        AND MWROL.WORKFLOW_RECORD_ID = WR.ID \n");        
        
        sqlStatTemp.append("        AND ( SOWC.ID = ? OR SOWC.ID = ? ) \n"); //SystemWorkflowConstant.DMS_CREATE_PUBLIC_DOCUMENT), SystemWorkflowConstant.DMS_CREATE_PAPER_DOCUMENT
        sqlStatTemp.append("        AND MWROL.OBJECT_TYPE = ? \n"); // GlobalConstant.OBJECT_TYPE_DOCUMENT
                
        sqlStatTemp.append("        AND MWROL.OBJECT_ID = DOC.ROOT_ID \n");
        sqlStatTemp.append("        AND DOC.RECORD_STATUS = ? \n"); //"A"
        sqlStatTemp.append("        AND ( WR.END_DATE >= ? OR WR.END_DATE IS NULL) \n");
        sqlStatTemp.append("        AND ( DOC.EFFECTIVE_END_DATE >= ? OR DOC.EFFECTIVE_END_DATE IS NULL) \n");
        
        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
          sqlStatTemp.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        // format the sql for any 'LIKE' statement contained
        sqlStatTemp = this.getFormattedSQL(sqlStatTemp.toString());

        // Get total number of record return.
        sqlStat = sqlStat.append(sqlStatTemp);
        sqlStat.append("UNION \n");
        // sqlStatCnt = this.getSelectCountSQL(sqlStatTemp);
        sqlStatCnt = sqlStatTemp;
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        i = 1;
        this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.DMS_CREATE_PUBLIC_DOCUMENT);        
        this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.DMS_CREATE_PAPER_DOCUMENT);        
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.OBJECT_TYPE_DOCUMENT);          
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
              searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, i++, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        while (rsCnt.next()) {
          // totalNumOfRecord += rsCnt.getInt(1);
          totalNumOfRecord++;// because of the select DISTINCT
        }

        try {
          rsCnt.close();
        } catch (Exception ignore) {
        } finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {
        } finally {
          preStatCnt = null;
        }

        
        /******************************************************************************************************
         *                              Public Folder Workflow End
         ******************************************************************************************************/
        
        /*****************************************************************************************************
         *                             Public Folder UDF Workflow Begin
         ******************************************************************************************************/
         //* Public Folder Related
         sqlStatTemp = new StringBuffer();
         sqlStatTemp.append("SELECT  DISTINCT WR.ID, WR.WORKFLOW_CODE, WR.WORKFLOW_VERSION, WR.WORKFLOW_GROUP_ID, WR.ALLOW_INTERRUPT, WR.INTERRUPT_WORKFLOW_RECORD_ID, WR.STATUS, WR.START_DATE, WR.END_DATE, WR.DESCRIPTION, WR.WORKFLOW_CATEGORY_ID, WR.DMS_PARENT_ID, WR.RECORD_STATUS, WR.UPDATE_COUNT, WR.CREATOR_ID, WR.CREATE_DATE, WR.UPDATER_ID, WR.UPDATE_DATE, SOWC.WORKFLOW_CATEGORY_NAME, -1 as RELATED_OBJECT_ID, '" + GlobalConstant.OBJECT_TYPE_UDF + "' as RELATED_OBJECT_TYPE, WR.WORKFLOW_CODE as RELATED_OBJECT_NAME \n");
         sqlStatTemp.append("FROM    WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_RECORD_OBJECT_LINK MWROL, DMS_DOCUMENT DOC \n");
         sqlStatTemp.append("WHERE   WR.WORKFLOW_CATEGORY_ID = SOWC.ID \n");        
         sqlStatTemp.append("        AND WR.ID IN (" + possibleWorkflowRecordIDs + ") \n");
         sqlStatTemp.append("        AND MWROL.WORKFLOW_RECORD_ID = WR.ID \n");     
         
         sqlStatTemp.append("        AND (SOWC.ID = ? OR SOWC.ID = ?) \n"); //SystemWorkflowConstant.DMS_CREATE_PUBLIC_UDF_DOCUMENT, SystemWorkflowConstant.DMS_CREATE_PAPER_UDF_DOCUMENT
         sqlStatTemp.append("        AND MWROL.OBJECT_TYPE = ? \n"); // GlobalConstant.OBJECT_TYPE_UDF         
         
         sqlStatTemp.append("        AND MWROL.OBJECT_ID = DOC.ROOT_ID \n");
         sqlStatTemp.append("        AND DOC.RECORD_STATUS = ? \n"); //"A"
         sqlStatTemp.append("        AND ( WR.END_DATE >= ? OR WR.END_DATE IS NULL) \n");
         sqlStatTemp.append("        AND ( DOC.EFFECTIVE_END_DATE >= ? OR DOC.EFFECTIVE_END_DATE IS NULL) \n");
         
         // Form the WHERE clause for filtering.
         if (searchForm.isSearchable()) {
           String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
           sqlStatTemp.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
         }

         // format the sql for any 'LIKE' statement contained
         sqlStatTemp = this.getFormattedSQL(sqlStatTemp.toString());

         // Get total number of record return.
         sqlStat = sqlStat.append(sqlStatTemp);
         sqlStat.append("UNION \n");
         // sqlStatCnt = this.getSelectCountSQL(sqlStatTemp);
         sqlStatCnt = sqlStatTemp;
         preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
         i = 1;         
         this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.DMS_CREATE_PUBLIC_UDF_DOCUMENT);
         this.setPrepareStatement(preStatCnt, i++, SystemWorkflowConstant.DMS_CREATE_PAPER_UDF_DOCUMENT);
         this.setPrepareStatement(preStatCnt, i++, GlobalConstant.OBJECT_TYPE_UDF);         
         this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
         this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
         this.setPrepareStatement(preStatCnt, i++, Utility.getCurrentTimestamp());
         if (searchForm.isSearchable()) {
           String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
               searchForm.getBasicSearchType());
           this.setPrepareStatement(preStatCnt, i++, searchKeyword);
         }
         rsCnt = preStatCnt.executeQuery();
         while (rsCnt.next()) {
           // totalNumOfRecord += rsCnt.getInt(1);
           totalNumOfRecord++;// because of the select DISTINCT
         }

         try {
           rsCnt.close();
         } catch (Exception ignore) {
         } finally {
           rsCnt = null;
         }
         try {
           preStatCnt.close();
         } catch (Exception ignore) {
         } finally {
           preStatCnt = null;
         }         
         /******************************************************************************************************
          *                              Public Folder UDF Workflow End
          ******************************************************************************************************/
        
				// GENERAL workflow
				sqlStatTemp = new StringBuffer();
				sqlStatTemp
						.append("SELECT  DISTINCT WR.ID, WR.WORKFLOW_CODE, WR.WORKFLOW_VERSION, WR.WORKFLOW_GROUP_ID, WR.ALLOW_INTERRUPT, WR.INTERRUPT_WORKFLOW_RECORD_ID, WR.STATUS, WR.START_DATE, WR.END_DATE, WR.DESCRIPTION, WR.WORKFLOW_CATEGORY_ID, WR.DMS_PARENT_ID, WR.RECORD_STATUS, WR.UPDATE_COUNT, WR.CREATOR_ID, WR.CREATE_DATE, WR.UPDATER_ID, WR.UPDATE_DATE, SOWC.WORKFLOW_CATEGORY_NAME, 0 as RELATED_OBJECT_ID, '' as RELATED_OBJECT_TYPE, '' as RELATED_OBJECT_NAME \n");
				sqlStatTemp.append("FROM    WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC \n");
				sqlStatTemp.append("WHERE   WR.WORKFLOW_CATEGORY_ID = SOWC.ID \n");
				sqlStatTemp.append("        AND SOWC.SYS_IND = ? \n");
				sqlStatTemp.append("        AND WR.ID in (" + possibleWorkflowRecordIDs + ") \n");
				// Form the WHERE clause for filtering.
				if (searchForm.isSearchable()) {
					String searchField = getSearchColumn(searchForm.getBasicSearchField(), "WR");
					sqlStatTemp.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStatTemp = this.getFormattedSQL(sqlStatTemp.toString());

				// Get total number of record return.
				sqlStat = sqlStat.append(sqlStatTemp);
				sqlStatCnt = this.getSelectCountSQL(sqlStatTemp);
				preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
				i = 1;
				this.setPrepareStatement(preStatCnt, i++, GlobalConstant.FALSE);
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStatCnt, i++, searchKeyword);
				}
				rsCnt = preStatCnt.executeQuery();
				if (rsCnt.next()) {
					totalNumOfRecord += rsCnt.getInt(1);
				}

				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}

				// Form the ORDER clause for sorting.
				if (searchForm.isSortable()) {
					String sortAttribute = searchForm.getSortAttribute();
					sqlStat=this.getUnionOrderSQL(sqlStat, sortAttribute, searchForm.getSortOrder());
					/*if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
						sqlStat.insert(0, "SELECT * FROM (");
						sqlStat.append(") DCI_UNI_TABLE ");

						if (sortAttribute.indexOf(".") >= 0) {
							sortAttribute = sortAttribute.substring(sortAttribute.indexOf(".") + 1);
						}
						sortAttribute = "DCI_UNI_TABLE." + sortAttribute;
					} else {
						if (sortAttribute.indexOf(".") < 0) {
							sortAttribute = sortAttribute;
						}
					}
					sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());*/
				}

				// Retrieve the result in row basis.
				log.debug(sqlStat);
				sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				i = 1;
				// * Form Related
				this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.FMB_SUBMISSION_BY_FORM);
				this.setPrepareStatement(preStat, i++, GlobalConstant.OBJECT_TYPE_FORM);
				this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStat, i++, searchKeyword);
				}
				// * Form Group Related
				this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.FMB_SUBMISSION_BY_GROUP);
				this.setPrepareStatement(preStat, i++, GlobalConstant.OBJECT_TYPE_FORMGROUP);
				this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_RELEASED);
				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStat, i++, searchKeyword);
				}
        
        /**********************************
         *  Public Folder Workflow Begin
         **********************************/
        this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.DMS_CREATE_PUBLIC_DOCUMENT);
        this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.DMS_CREATE_PAPER_DOCUMENT);
        this.setPrepareStatement(preStat, i++, GlobalConstant.OBJECT_TYPE_DOCUMENT);        
        this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
              searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, i++, searchKeyword);
        }
        /**********************************
         *  Public Folder Workflow End
         **********************************/
        
        /**********************************
         *  Public Folder UDF Workflow Begin
         **********************************/
        this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.DMS_CREATE_PUBLIC_UDF_DOCUMENT);
        this.setPrepareStatement(preStat, i++, SystemWorkflowConstant.DMS_CREATE_PAPER_UDF_DOCUMENT);
        this.setPrepareStatement(preStat, i++, GlobalConstant.OBJECT_TYPE_UDF);        
        this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
        this.setPrepareStatement(preStat, i++, Utility.getCurrentTimestamp());
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
              searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, i++, searchKeyword);
        }
        /**********************************
         *  Public Folder UDF Workflow End
         **********************************/
        
        // * Genderal Workflow
				this.setPrepareStatement(preStat, i++, GlobalConstant.FALSE);

				if (searchForm.isSearchable()) {
					String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(),
							searchForm.getBasicSearchType());
					this.setPrepareStatement(preStat, i++, searchKeyword);
				}

        log.debug("##############################################");
        log.debug(sqlStat.toString());
        log.debug("##############################################");
        
				rs = preStat.executeQuery();
        this.positionCursor(rs,startOffset,pageSize);
				while (rs.next() && rowLoopCnt < pageSize) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRelatedObjectID(getResultSetInteger(rs, "RELATED_OBJECT_ID"));
					tmpWorkflowRecord.setRelatedObjectType(getResultSetString(rs, "RELATED_OBJECT_TYPE"));
					tmpWorkflowRecord.setRelatedObjectName(getResultSetString(rs, "RELATED_OBJECT_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					tmpWorkflowRecord.setRecordCount(totalNumOfRecord);

					// get the binding form list
					List bindingFormList = formRecordDAO.getBindFormListByWorkflowRecordID(tmpWorkflowRecord
							.getID());
					if (!Utility.isEmpty(bindingFormList)) {
						tmpWorkflowRecord.setBindingFormList(bindingFormList);
						tmpWorkflowRecord.setBindingFormListSize(bindingFormList.size());
					}
          /************************************************
           *  Get the public folder list.
           ************************************************/
          if ( GlobalConstant.OBJECT_TYPE_DOCUMENT.equals(tmpWorkflowRecord.getRelatedObjectType()) ) {
            List folderList = dmsDAO.getBindFolderListByWorkflowRecordID(tmpWorkflowRecord.getID());
            if (!Utility.isEmpty(folderList)) {
              tmpWorkflowRecord.setBindingDmsFolderList(folderList);
              tmpWorkflowRecord.setBindingDmsFolderListSize(folderList.size());
            }
            
          /************************************************
           *  Get the profile list.
           ************************************************/  
          } else if ( GlobalConstant.OBJECT_TYPE_UDF.equals(tmpWorkflowRecord.getRelatedObjectType()) ) {            
            List profileList = profileDAO.getBindProfileListByWorkflowRecordID(tmpWorkflowRecord.getID());
            if (!Utility.isEmpty(profileList)) {
              tmpWorkflowRecord.setBindingProfileList(profileList);
              tmpWorkflowRecord.setBindingProfileListSize(profileList.size());
            }
          }
					
          //Get the second step's allow_assign_due_date
          int count = 0;
          tmpWorkflowRecord.setNextStepDynamicAssignDueDate(GlobalConstant.FALSE);
          WorkflowStep wfStep = (WorkflowStep) stepDAO.getSubmitStepByWorkflowRecordID(tmpWorkflowRecord.getID());
          List mtmStepList = stepStepDAO.getListByParentStepID(wfStep.getID());

          for (int j = 0; j < mtmStepList.size(); j++) {
            MtmWorkflowStepWorkflowStep stepStep = (MtmWorkflowStepWorkflowStep) mtmStepList.get(j);
            WorkflowStep tmpWfStep = (WorkflowStep) stepDAO.getObjectByID(stepStep.getChildStepID());
            if (WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_TRUE.equals(tmpWfStep.getAllowAssignDueDate()) || WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_MANDATORY.equals(tmpWfStep.getAllowAssignDueDate())) {
              tmpWorkflowRecord.setNextStepDynamicAssignDueDate(tmpWfStep.getAllowAssignDueDate());
              count++;
            }
          }
          tmpWorkflowRecord.setNextStepDynamicAssignDueDateCount(count);
                  
					tmpWorkflowRecord.setRowNum(startOffset++);
					++rowLoopCnt;
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
				try {
					rsCnt.close();
				} catch (Exception ignore) {
				} finally {
					rsCnt = null;
				}
				try {
					preStatCnt.close();
				} catch (Exception ignore) {
				} finally {
					preStatCnt = null;
				}
			}
		}
	}

	/**
	 * Gets a list of workflow record that the current user can submit under the
	 * given system category. The user record is pre-set in the SessionContainer
	 * object during the creation of the DAObject,
	 * 
	 * <pre>
	 * sessionContainer.setUserRecord(userRecord);
	 * </pre>
	 * 
	 * 
	 * @param workflowCategoryID
	 *          The system category ID
	 * @return List of matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListCanSubmit(Integer workflowCategoryID) throws ApplicationException {
		PermissionManager pm = sessionContainer.getPermissionManager();
		Integer userRecordID = sessionContainer.getUserRecordID();
		List userRolesID = pm.getUserRoles();
		List userGroupsID = pm.getUserGroups();
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();
		int totalNumOfRecord = 0;

		WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(sessionContainer, dbConn);
		MtmWorkflowStepWorkflowStepDAObject stepStepDAO = new MtmWorkflowStepWorkflowStepDAObject(
				sessionContainer, dbConn);

		synchronized (dbConn) {
			try {
				sqlStat.append("SELECT  DISTINCT WR.*, SOWC.WORKFLOW_CATEGORY_NAME \n");
				sqlStat
						.append("FROM    WORKFLOW_STEP WS, WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_STEP_USER_ACTOR MWSUA \n");
				sqlStat.append("WHERE   WS.ACTION_TYPE = ? AND \n");
				sqlStat.append("        MWSUA.ACTION_TYPE = ? AND \n");
				sqlStat.append("        WR.ID = WS.WORKFLOW_RECORD_ID AND \n");
				sqlStat.append("        WR.WORKFLOW_CATEGORY_ID = SOWC.ID AND \n");
				sqlStat.append("        WR.RECORD_STATUS = ? AND \n");
				sqlStat.append("        SOWC.RECORD_STATUS = ? AND \n");
				sqlStat.append("        WR.STATUS = ? AND \n");
				sqlStat.append("        WS.ID = MWSUA.WORKFLOW_STEP_ID AND \n");
				sqlStat.append("        ? >= WR.START_DATE AND \n");
				sqlStat.append("        (? < WR.END_DATE OR WR.END_DATE IS NULL) AND \n");
				sqlStat
						.append("        (MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ?  OR MWSUA.ACTOR_TYPE = ? \n");
				String tmpStr = "";
				for (int i = 0; userGroupsID != null && i < userGroupsID.size(); i++) {
					if (i != 0) {
						tmpStr += ", ";
					}
					UserGroup group = (UserGroup) userGroupsID.get(i);
					tmpStr += group.getID();
				}
				if (!"".equals(tmpStr)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"
							+ MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND MWSUA.ACTOR_ID IN (" + tmpStr
							+ ") \n");
				}
				tmpStr = "";
				for (int i = 0; userRolesID != null && i < userRolesID.size(); i++) {
					if (i != 0) {
						tmpStr += ", ";
					}
					UserRole role = (UserRole) userRolesID.get(i);
					tmpStr += role.getID();
				}
				if (!"".equals(tmpStr)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"
							+ MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND MWSUA.ACTOR_ID IN (" + tmpStr
							+ ") \n");
				}
				sqlStat.append("        ) ");
				if (workflowCategoryID == null || workflowCategoryID.intValue() < 0) {
					sqlStat.append(" AND SOWC.SYS_IND = 'N' ");
				} else {
					sqlStat.append(" AND SOWC.ID = " + workflowCategoryID + " ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStat = this.getFormattedSQL(sqlStat.toString());

				sqlStat.append("ORDER BY WR.WORKFLOW_CODE DESC ");

				// Retrieve the result in row basis.
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

				Timestamp currTime = Utility.getCurrentTimestamp();
				//Timestamp tmrTime = new Timestamp(currTime.getTime() + 24 * 60 * 60 * 1000L);
        //tc delete. getListCanSubmit means this workflow can submit this time.
        //so just need start-time<=current time<end time
        //and the mini life cycle is 00:00:00 to 23:59:59,so the tmrTime is meaningless
				log.debug(WorkflowStep.ACTION_TYPE_SUBMIT);
				log.debug(MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				log.debug(GlobalConstant.STATUS_ACTIVE);
				log.debug(GlobalConstant.STATUS_ACTIVE);
				log.debug(GlobalConstant.RECORD_RELEASED);
				log.debug(currTime);
				log.debug(currTime);
				log.debug(MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				log.debug(userRecordID);
				this.setPrepareStatement(preStat, 1, WorkflowStep.ACTION_TYPE_SUBMIT);
				this.setPrepareStatement(preStat, 2, MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 4, GlobalConstant.STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_RELEASED);
				this.setPrepareStatement(preStat, 6, currTime);
				this.setPrepareStatement(preStat, 7, currTime);
				this.setPrepareStatement(preStat, 8, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				this.setPrepareStatement(preStat, 9, userRecordID);
				this.setPrepareStatement(preStat, 10, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);
				rs = preStat.executeQuery();

				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					tmpWorkflowRecord.setRecordCount(totalNumOfRecord);

					// Get the second step's allow_assign_due_date
					int count = 0;
					tmpWorkflowRecord.setNextStepDynamicAssignDueDate(GlobalConstant.FALSE);
					WorkflowStep wfStep = (WorkflowStep) stepDAO
							.getSubmitStepByWorkflowRecordID(tmpWorkflowRecord.getID());
					List mtmStepList = stepStepDAO.getListByParentStepID(wfStep.getID());

					for (int j = 0; j < mtmStepList.size(); j++) {
						MtmWorkflowStepWorkflowStep stepStep = (MtmWorkflowStepWorkflowStep) mtmStepList.get(j);
						WorkflowStep tmpWfStep = (WorkflowStep) stepDAO
								.getObjectByID(stepStep.getChildStepID());
						if (WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_TRUE.equals(tmpWfStep.getAllowAssignDueDate())
								|| WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_MANDATORY.equals(tmpWfStep
										.getAllowAssignDueDate())) {
							tmpWorkflowRecord.setNextStepDynamicAssignDueDate(tmpWfStep.getAllowAssignDueDate());
							count++;
						}
					}
					tmpWorkflowRecord.setNextStepDynamicAssignDueDateCount(count);

					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets a list of workflow record having the specified workflow code and
	 * version and within the given workflow group
	 * 
	 * @param workflowCode
	 *          The workflow code
	 * @param workflowVersion
	 *          The workflow version
	 * @param groupID
	 *          The workflow group ID
	 * @return The matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowCodeWorkflowVersionGroupID(String workflowCode,
			String workflowVersion, Integer groupID) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID,A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
				sqlStat
						.append("AND A.WORKFLOW_CODE = ? AND A.WORKFLOW_VERSION = ? AND A.WORKFLOW_GROUP_ID = ? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 2, workflowCode);
				this.setPrepareStatement(preStat, 3, workflowVersion);
				this.setPrepareStatement(preStat, 4, groupID);
				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}
	/**
	 * Gets a list of workflow record having the specified workflow code and Version
	 *
	 * @param workflowCode
	 *          The workflow code
	 * @param workflowVersion
	 *          The workflow version
	 * @return The matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
  public synchronized List getListByWorkflowCodeWorkflowVersion(String workflowCode,
      String workflowVersion) throws ApplicationException {
      return getListByWorkflowCodeWorkflowVersion(workflowCode,workflowVersion,null);
  }
  public synchronized List getListByWorkflowCodeWorkflowVersion(String workflowCode,
			String workflowVersion,Integer workflowRecordID) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		synchronized (dbConn) {
			try {
				sqlStat.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID,A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append(" FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append(" WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
				sqlStat.append(" AND A.WORKFLOW_CODE = ? AND A.WORKFLOW_VERSION = ? ");
				if(!Utility.isEmpty(workflowRecordID)){
          sqlStat.append(" AND A.ID <> ?");    
        }
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 2, workflowCode);
				this.setPrepareStatement(preStat, 3, workflowVersion);
        if(!Utility.isEmpty(workflowRecordID)){
          this.setPrepareStatement(preStat, 4, workflowRecordID); 
        }
				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}
	/**
	 * Gets a list of workflow record having the specified workflow code within
	 * the given workflow group
	 * 
	 * @param workflowCode
	 *          The workflow code
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @return The matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowCodeWorkflowGroupID(String workflowCode,
			Integer workflowGroupID) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID,A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
				sqlStat.append("AND A.WORKFLOW_CODE = ? AND A.WORKFLOW_GROUP_ID = ? ");
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 2, workflowCode);
				this.setPrepareStatement(preStat, 3, workflowGroupID);
				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Validates the insert or update of the given workflow record object
	 * 
	 * @param obj
	 *          The workflow record object
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	protected void validateInsertOrUpdate(AbstractBaseObject obj) throws ApplicationException {
		WorkflowRecord workflowRecord = (WorkflowRecord) obj;
		ApplicationExceptionList exceptionList = new ApplicationExceptionList();
		WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(sessionContainer, dbConn);

		List existRecordWithSameNameAndVersion = this.getListByWorkflowCodeWorkflowVersion(
				workflowRecord.getWorkflowCode(), workflowRecord.getWorkflowVersion());
		for (int i = 0; i < existRecordWithSameNameAndVersion.size(); i++) {
			WorkflowRecord existRecord = (WorkflowRecord) existRecordWithSameNameAndVersion.get(i);
			if (!existRecord.getID().equals(workflowRecord.getID())) {
				exceptionList.addException(new ApplicationException(
						WorkflowErrorConstant.DUPLICATE_WORKFLOW_CODE_VERSION));
				break;
			}
		}

		List existRecordWithSameName = this.getListByWorkflowCodeWorkflowGroupID(workflowRecord
				.getWorkflowCode(), workflowRecord.getWorkflowGroupID());
		// check with the effective date
		Timestamp newStartDate = workflowRecord.getStartDate();
		Timestamp newEndDate = workflowRecord.getEndDate();
		for (int i = 0; i < existRecordWithSameName.size(); i++) {
			WorkflowRecord existRecord = (WorkflowRecord) existRecordWithSameName.get(i);
			if (existRecord.getID().equals(workflowRecord.getID())) {
				continue;
			}
			Timestamp startDate = existRecord.getStartDate();
			Timestamp endDate = existRecord.getEndDate();
			if (!Utility.isEmpty(endDate) && startDate.compareTo(newStartDate) <= 0
					&& endDate.compareTo(newStartDate) >= 0) {
				exceptionList.addException(new ApplicationException(
						WorkflowErrorConstant.EXIST_WORKFLOW_RECORD_IS_EFFECTIVE));
				break;
			} else if (newEndDate != null && !Utility.isEmpty(endDate)
					&& startDate.compareTo(newEndDate) <= 0 && endDate.compareTo(newStartDate) >= 0) {
				exceptionList.addException(new ApplicationException(
						WorkflowErrorConstant.EXIST_WORKFLOW_RECORD_IS_EFFECTIVE));
				break;
			} else if (startDate.compareTo(newStartDate) <= 0 && endDate == null) {
				//remark by sars
				/*if (WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(existRecord.getStatus())
						&& WorkflowRecord.WORKFLOW_RECORD_RELEASE.equals(workflowRecord.getStatus())) {
					existRecord.setEndDate(Utility.addMinute(newStartDate, -1));
					wfRecordDAO.updateObject(existRecord);
				}*/
			}
		}

		exceptionList.throwException();
	}

	/**
	 * Gets a list of workflow record within the given workflow group
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @return The matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowGroupID(Integer workflowGroupID)
			throws ApplicationException {
		return this.getListByWorkflowGroupIDWorkflowCategoryID(workflowGroupID, null);
	}

	/**
	 * Gets a list of workflow record that the current user can submit under the
	 * given system category, and the workflow record should binded with the
	 * specified object type and ID. The user record is pre-set in the
	 * SessionContainer object during the creation of the DAObject,
	 * 
	 * <pre>
	 * sessionContainer.setUserRecord(userRecord);
	 * </pre>
	 * 
	 * @param workflowCategoryID
	 *          The system category ID
	 * @param objectID
	 *          The object ID
	 * @param objectType
	 *          The object type
	 * @return List of matched workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.framework.GlobalConstant#OBJECT_TYPE_DOCUMENT
	 * @see com.dcivision.framework.GlobalConstant#OBJECT_TYPE_FORM
	 * @see com.dcivision.framework.GlobalConstant#OBJECT_TYPE_WORKFLOW
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListCanSubmit(Integer workflowCategoryID, Integer objectID,
			String objectType) throws ApplicationException {
		PermissionManager pm = sessionContainer.getPermissionManager();
		Integer userRecordID = sessionContainer.getUserRecordID();
		List userRolesID = pm.getUserRoles();
		List userGroupsID = pm.getUserGroups();
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();
		int totalNumOfRecord = 0;

		WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(sessionContainer, dbConn);
		MtmWorkflowStepWorkflowStepDAObject stepStepDAO = new MtmWorkflowStepWorkflowStepDAObject(
				sessionContainer, dbConn);

		synchronized (dbConn) {
			try {
				sqlStat.append("SELECT  DISTINCT WR.*, SOWC.WORKFLOW_CATEGORY_NAME \n");
				sqlStat
						.append("FROM    WORKFLOW_STEP WS, WORKFLOW_RECORD WR, SETUP_OPTION_WORKFLOW_CATEGORY SOWC, MTM_WF_STEP_USER_ACTOR MWSUA, MTM_WF_RECORD_OBJECT_LINK MWROL  \n");
				sqlStat.append("WHERE   WS.ACTION_TYPE = ? AND \n");
				sqlStat.append("        MWSUA.ACTION_TYPE = ? AND \n");
				sqlStat.append("        WR.ID = WS.WORKFLOW_RECORD_ID AND \n");
				sqlStat.append("        WR.WORKFLOW_CATEGORY_ID = SOWC.ID AND \n");
				sqlStat.append("        WR.RECORD_STATUS = ? AND \n");
				sqlStat.append("        SOWC.RECORD_STATUS = ? AND \n");
				sqlStat.append("        WR.STATUS = ? AND \n");
				sqlStat.append("        WS.ID = MWSUA.WORKFLOW_STEP_ID AND \n");
				sqlStat.append("        ? >= WR.START_DATE AND \n");
				sqlStat.append("        ( ? <= WR.END_DATE OR WR.END_DATE IS NULL) AND \n");
				sqlStat.append("        WR.ID = MWROL.WORKFLOW_RECORD_ID AND \n");
				sqlStat.append("        WS.RECORD_STATUS = MWROL.RECORD_STATUS AND \n ");
				sqlStat.append("        MWROL.OBJECT_ID = " + objectID + " AND \n");
				sqlStat.append("        MWROL.OBJECT_TYPE = '" + objectType + "' AND \n");
				sqlStat.append("        (MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ? \n");
				sqlStat.append("        OR MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ? \n");

				String tmpStr = "";
				for (int i = 0; userGroupsID != null && i < userGroupsID.size(); i++) {
					if (i != 0) {
						tmpStr += ", ";
					}
					UserGroup group = (UserGroup) userGroupsID.get(i);
					tmpStr += group.getID();
				}
				if (!"".equals(tmpStr)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"
							+ MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND MWSUA.ACTOR_ID IN (" + tmpStr
							+ ") \n");
				}
				tmpStr = "";
				for (int i = 0; userRolesID != null && i < userRolesID.size(); i++) {
					if (i != 0) {
						tmpStr += ", ";
					}
					UserRole role = (UserRole) userRolesID.get(i);
					tmpStr += role.getID();
				}
				if (!"".equals(tmpStr)) {
					sqlStat.append("         OR MWSUA.ACTOR_TYPE = '"
							+ MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND MWSUA.ACTOR_ID IN (" + tmpStr
							+ ") \n");
				}
				sqlStat.append("        ) ");
				if (workflowCategoryID == null || workflowCategoryID.intValue() < 0) {
					sqlStat.append(" AND SOWC.SYS_IND = 'N' ");
				} else {
					sqlStat.append(" AND SOWC.ID = " + workflowCategoryID + " ");
				}

				// format the sql for any 'LIKE' statement contained
				sqlStat = this.getFormattedSQL(sqlStat.toString());

				sqlStat.append("ORDER BY WR.WORKFLOW_CODE DESC ");

				// Retrieve the result in row basis.
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

				Calendar currCal = Calendar.getInstance();
				currCal.set(Calendar.HOUR_OF_DAY, 0);
				currCal.set(Calendar.MINUTE, 0);
				currCal.set(Calendar.SECOND, 0);
				currCal.set(Calendar.MILLISECOND, 0);

				Timestamp currTime = Utility.calendarToTimestamp(currCal);

				Timestamp tmrTime = new Timestamp(currTime.getTime() + (24 * 60 * 60 - 1) * 1000L);
				log.debug(WorkflowStep.ACTION_TYPE_SUBMIT);
				log.debug(MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				log.debug(GlobalConstant.STATUS_ACTIVE);
				log.debug(GlobalConstant.STATUS_ACTIVE);
				log.debug(GlobalConstant.RECORD_RELEASED);
				log.debug(currTime);
				log.debug(tmrTime);
				log.debug(MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				log.debug(userRecordID);
				this.setPrepareStatement(preStat, 1, WorkflowStep.ACTION_TYPE_SUBMIT);
				this.setPrepareStatement(preStat, 2, MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
				this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 4, GlobalConstant.STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_RELEASED);
				this.setPrepareStatement(preStat, 6, currTime);
				this.setPrepareStatement(preStat, 7, tmrTime);
				this.setPrepareStatement(preStat, 8, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
				this.setPrepareStatement(preStat, 9, userRecordID);
				this.setPrepareStatement(preStat, 10, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);
				this.setPrepareStatement(preStat, 11, new Integer(0));
				rs = preStat.executeQuery();

				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					tmpWorkflowRecord.setRecordCount(totalNumOfRecord);

					// Get the second step's allow_assign_due_date
					int count = 0;
					tmpWorkflowRecord.setNextStepDynamicAssignDueDate(GlobalConstant.FALSE);
					WorkflowStep wfStep = (WorkflowStep) stepDAO
							.getSubmitStepByWorkflowRecordID(tmpWorkflowRecord.getID());
					List mtmStepList = stepStepDAO.getListByParentStepID(wfStep.getID());

					for (int j = 0; j < mtmStepList.size(); j++) {
						MtmWorkflowStepWorkflowStep stepStep = (MtmWorkflowStepWorkflowStep) mtmStepList.get(j);
						WorkflowStep tmpWfStep = (WorkflowStep) stepDAO
								.getObjectByID(stepStep.getChildStepID());
						if (WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_TRUE.equals(tmpWfStep.getAllowAssignDueDate())
								|| WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_MANDATORY.equals(tmpWfStep
										.getAllowAssignDueDate())) {
							tmpWorkflowRecord.setNextStepDynamicAssignDueDate(tmpWfStep.getAllowAssignDueDate());
							count++;
						}
					}
					tmpWorkflowRecord.setNextStepDynamicAssignDueDateCount(count);

					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets a list of workflow record that is under the given workflow group with
	 * the given system category ID and record status
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @param workflowCategoryID
	 *          The system category ID (null if ignore)
	 * @param workflowRecordStatus
	 *          The record status (null if ignore)
	 * @return List of matched workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_ACTIVE
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_PENDING
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(
			Integer workflowGroupID, Integer workflowCategoryID, String workflowRecordStatus)
			throws ApplicationException {
		return getListByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(workflowGroupID,
				workflowCategoryID, workflowRecordStatus, null);
	}

	/**
	 * Gets a list of workflow record that is under the given workflow group with
	 * the given system category ID and record status
	 * 
	 * @param workflowGroupID The workflow group ID
	 * @param workflowCategoryID The system category ID (null if ignore)
	 * @param workflowRecordStatus The record status (null if ignore)
	 * @param current List the workflow that not expired
	 * @return List of matched workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_ACTIVE
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_PENDING
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(
			Integer workflowGroupID, Integer workflowCategoryID, String workflowRecordStatus,
			String current) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		synchronized (dbConn) {
			try {
				sqlStat.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
				sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
				
        if (!Utility.isEmpty(workflowGroupID)) {
          sqlStat.append(" AND A.WORKFLOW_GROUP_ID = ? ");
        }
        
				if (!Utility.isEmpty(workflowCategoryID)) {
					sqlStat.append(" AND B.ID = ? ");
				}

				if (!Utility.isEmpty(workflowRecordStatus)) {
					sqlStat.append(" AND A.STATUS = '" + workflowRecordStatus + "'");
				}
        
				if (!Utility.isEmpty(current)) {
					sqlStat.append(" AND (A.END_DATE >= ? OR A.END_DATE  IS NULL )");
				}

				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				int i = 1;
        this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        
        if (!Utility.isEmpty(workflowGroupID)) {
				  this.setPrepareStatement(preStat, i++, workflowGroupID);
        }

				if (!Utility.isEmpty(workflowCategoryID)) {
					this.setPrepareStatement(preStat, i++, workflowCategoryID);
					if (!Utility.isEmpty(current)) {
						this.setPrepareStatement(preStat, i++, TextUtility.formatTimestampToDBDate(Utility
								.getCurrentTimestamp()));
					}
				} else {
					if (!Utility.isEmpty(current)) {
						this.setPrepareStatement(preStat, i++, TextUtility.formatTimestampToDBDate(Utility
								.getCurrentTimestamp()));
					}
				}

				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					result.add(tmpWorkflowRecord );

				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets the number of active workflow record under the given workflow group
	 * with the given system category and record status
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @param workflowCategoryID
	 *          The system category ID (null if ignore)
	 * @param workflowRecordStatus
	 *          The wokkflow record status (null if ignore)
	 * @return Number of matched active workflow record
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_ACTIVE
	 * @see com.dcivision.framework.GlobalConstant#RECORD_STATUS_PENDING
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized Integer getNoOfActiveRecordByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(
			Integer workflowGroupID, Integer workflowCategoryID, String workflowRecordStatus)
			throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();

		synchronized (dbConn) {
			try {
				sqlStat.append("SELECT COUNT(*) as NO_OF_ACTIVE_RECORDS ");
				sqlStat.append("FROM   WORKFLOW_RECORD A ");
				sqlStat.append("WHERE  A.WORKFLOW_GROUP_ID = ? AND A.RECORD_STATUS = ? ");
				if (!Utility.isEmpty(workflowCategoryID)) {
					sqlStat.append("AND    A.WORKFLOW_CATEGORY_ID = ? ");
				}

				if (!Utility.isEmpty(workflowRecordStatus)) {
					sqlStat.append("AND A.STATUS = '" + workflowRecordStatus + "'");
				}

				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, workflowGroupID);
				this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
				if (!Utility.isEmpty(workflowCategoryID)) {
					this.setPrepareStatement(preStat, 3, workflowCategoryID);
				}
				rs = preStat.executeQuery();
				if (rs.next()) {
					return (getResultSetInteger(rs, "NO_OF_ACTIVE_RECORDS"));
				} else {
					throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
				}
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets a list of workflow record that is under the given workflow group with
	 * the given system category ID
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @param workflowCategoryID
	 *          The system category ID (null if ignore)
	 * @return List of matched workflow records
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListByWorkflowGroupIDWorkflowCategoryID(Integer workflowGroupID,
			Integer workflowCategoryID) throws ApplicationException {
		return this.getListByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(workflowGroupID,
				workflowCategoryID, null);
	}

	/**
	 * Gets the number of active workflow record under the given workflow group
	 * with the given system category
	 * 
	 * @param workflowGroupID
	 *          The workflow group ID
	 * @param workflowCategoryID
	 *          The system category ID (null if ignore)
	 * @return Number of matched active workflow record
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized Integer getNoOfActiveRecordByWorkflowGroupIDWorkflowCategoryID(
			Integer workflowGroupID, Integer workflowCategoryID) throws ApplicationException {
		return this.getNoOfActiveRecordByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(
				workflowGroupID, workflowCategoryID, null);
	}

	/**
	 * Gets a list of workflow record that the current user can submit under the
	 * given workflow record. The user record is pre-set in the SessionContainer
	 * object during the creation of the DAObject,
	 * 
	 * <pre>
	 * sessionContainer.setUserRecord(userRecord);
	 * </pre>
	 * 
	 * 
	 * @param formRecordID
	 *          The workflow record ID
	 * @return List of matched workflow record objects
	 * @throws ApplicationException
	 * @see com.dcivision.setup.bean.SetupOptionWorkflowCategory
	 * @see com.dcivision.workflow.bean.WorkflowRecord
	 */
	public synchronized List getListCanSubmitByFormRecordID(Integer formRecordID)
			throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();

		WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(sessionContainer, dbConn);
		MtmWorkflowStepWorkflowStepDAObject stepStepDAO = new MtmWorkflowStepWorkflowStepDAObject(
				sessionContainer, dbConn);

		synchronized (dbConn) {
			try {
				sqlStat
						.append(" SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append(" FROM   WORKFLOW_RECORD A, MTM_WF_RECORD_OBJECT_LINK B ");
				sqlStat.append(" WHERE  A.ID = B.WORKFLOW_RECORD_ID ");
				sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
				sqlStat.append(" AND A.RECORD_STATUS = ? ");
				sqlStat.append(" AND B.OBJECT_TYPE = ? ");
				sqlStat.append(" AND B.OBJECT_ID = ? ");

				sqlStat.append(" UNION ");

				sqlStat
						.append(" SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append(" FROM   WORKFLOW_RECORD A, MTM_WF_RECORD_OBJECT_LINK B, FORM_RECORD C ");
				sqlStat.append(" WHERE  A.ID = B.WORKFLOW_RECORD_ID ");
				sqlStat.append(" AND C.FORM_GROUP_ID = B.OBJECT_ID ");
				sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
				sqlStat.append(" AND A.RECORD_STATUS = C.RECORD_STATUS ");
				sqlStat.append(" AND A.RECORD_STATUS = ? ");
				sqlStat.append(" AND B.OBJECT_TYPE = ? ");
				sqlStat.append(" AND C.ID = ? ");

				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

				this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 2, GlobalConstant.OBJECT_TYPE_FORM);
				this.setPrepareStatement(preStat, 3, formRecordID);

				this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
				this.setPrepareStatement(preStat, 5, GlobalConstant.OBJECT_TYPE_FORMGROUP);
				this.setPrepareStatement(preStat, 6, formRecordID);

				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));

					// Get the second step's allow_assign_due_date
					int count = 0;
					tmpWorkflowRecord.setNextStepDynamicAssignDueDate(GlobalConstant.FALSE);
					WorkflowStep wfStep = (WorkflowStep) stepDAO
							.getSubmitStepByWorkflowRecordID(tmpWorkflowRecord.getID());
					List mtmStepList = stepStepDAO.getListByParentStepID(wfStep.getID());

					for (int j = 0; j < mtmStepList.size(); j++) {
						MtmWorkflowStepWorkflowStep stepStep = (MtmWorkflowStepWorkflowStep) mtmStepList.get(j);
						WorkflowStep tmpWfStep = (WorkflowStep) stepDAO
								.getObjectByID(stepStep.getChildStepID());
						if (WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_TRUE.equals(tmpWfStep.getAllowAssignDueDate())
								|| WorkflowStep.DYNAMIC_ASSIGN_DUE_DATE_MANDATORY.equals(tmpWfStep
										.getAllowAssignDueDate())) {
							tmpWorkflowRecord.setNextStepDynamicAssignDueDate(tmpWfStep.getAllowAssignDueDate());
							count++;
						}
					}
					tmpWorkflowRecord.setNextStepDynamicAssignDueDateCount(count);

					result.add(tmpWorkflowRecord);
				}
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets the system object of effective workflow record under the given
	 * workflow code.
	 * 
	 * @param workflowCode
	 *          The workflow code
	 * @return The system object(tmpWorkflowRecord)
	 * @throws ApplicationException
	 * @see com.dcivision.workflow.bean.WorkflowGroup
	 */
	public synchronized AbstractBaseObject getEffectiveWorkflowRecordByWorkflowCode(
			String workflowCode) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
		synchronized (dbConn) {
			try {
				sqlStat
						.append(" SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append(" FROM   WORKFLOW_RECORD A ");
				sqlStat.append(" WHERE  A.WORKFLOW_CODE=? ");
				if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
					sqlStat.append(" AND ( A.START_DATE <= to_date(? , 'YYYY-MM-DD HH24:MI:SS')");
					sqlStat
							.append(" AND ( A.END_DATE IS NULL OR A.END_DATE  >= to_date(? , 'YYYY-MM-DD HH24:MI:SS')))");
				} else {
					sqlStat.append(" AND ( A.START_DATE <= ? ");
					sqlStat.append(" AND ( A.END_DATE IS NULL OR A.END_DATE  >= ? ))");
				}
				sqlStat.append(" AND A.RECORD_STATUS = ?");
				log.debug("========================" + sqlStat.toString());
				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

				this.setPrepareStatement(preStat, 1, workflowCode);
				this.setPrepareStatement(preStat, 2, TextUtility.formatTimestampToDBDate(Utility
						.getCurrentTimestamp()));
				this.setPrepareStatement(preStat, 3, TextUtility.formatTimestampToDBDate(Utility
						.getCurrentTimestamp()));
				this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
				rs = preStat.executeQuery();
				if (rs.next()) {
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					// tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs,
					// "WORKFLOW_CATEGORY_NAME"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
					return tmpWorkflowRecord;
				}
				return null;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}

	/**
	 * Gets the list of workflows binding having the workflow record and the
	 * workflow group.
	 * 
	 * @param formRecordID
	 *          The workflow record ID
	 * @param formGroupID
	 *          The workflow group ID
	 * @return bind workflow list
	 * @throws ApplicationException
	 */
	public synchronized List getBindListByFormRecordIDFormGroupID(Integer formRecordID,
			Integer formGroupID) throws ApplicationException {
		PreparedStatement preStat = null;
		ResultSet rs = null;
		StringBuffer sqlStat = new StringBuffer();
		List result = new ArrayList();
    MtmWfStepFormRecordDAObject stepFormRecordDAO = new MtmWfStepFormRecordDAObject(this.sessionContainer, dbConn);
    
		synchronized (dbConn) {
			try {
				sqlStat
						.append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
				sqlStat.append("FROM   WORKFLOW_RECORD A, MTM_WF_RECORD_OBJECT_LINK B ");
				sqlStat.append("WHERE  A.RECORD_STATUS = B.RECORD_STATUS ");
				sqlStat.append("AND    A.ID = B.WORKFLOW_RECORD_ID ");
				sqlStat.append("AND    (( B.OBJECT_TYPE = ? AND B.OBJECT_ID = ? )  OR ");
				sqlStat.append("        ( B.OBJECT_TYPE = ? AND B.OBJECT_ID = ? ) ) ");
				sqlStat.append("AND    A.STATUS = ? ");
				sqlStat.append("AND    A.RECORD_STATUS = ? ");

				preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				this.setPrepareStatement(preStat, 1, GlobalConstant.OBJECT_TYPE_FORM);
				this.setPrepareStatement(preStat, 2, formRecordID);
				this.setPrepareStatement(preStat, 3, GlobalConstant.OBJECT_TYPE_FORMGROUP);
				this.setPrepareStatement(preStat, 4, formGroupID);
				this.setPrepareStatement(preStat, 5, WorkflowRecord.WORKFLOW_RECORD_RELEASE);
				this.setPrepareStatement(preStat, 6, GlobalConstant.RECORD_STATUS_ACTIVE);

				rs = preStat.executeQuery();
				while (rs.next()) {
					WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
					tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
					tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
					tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
					tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
					tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
					tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
							"INTERRUPT_WORKFLOW_RECORD_ID"));
					tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
					tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
					tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
					tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
					tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
					tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
					tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
					tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
					tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
					tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
					tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
					tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
					tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getCreatorID()));
					tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
							.getUpdaterID()));
          
          //check if the form bind with the worfklow at the first step
          List bindList = stepFormRecordDAO.getListByWorkflowRecordIDFormRecordID(tmpWorkflowRecord.getID(), formRecordID);
          tmpWorkflowRecord.setHasBindFormFistStep(Utility.isEmpty(bindList) ? GlobalConstant.FALSE : GlobalConstant.TRUE);
          result.add(tmpWorkflowRecord);

        }
        
				return (result);
			} catch (ApplicationException appEx) {
				throw appEx;
			} catch (SQLException sqle) {
				log.error(sqle, sqle);
				throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
			} catch (Exception e) {
				log.error(e, e);
				throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
			} finally {
				try {
					rs.close();
				} catch (Exception ignore) {
				} finally {
					rs = null;
				}
				try {
					preStat.close();
				} catch (Exception ignore) {
				} finally {
					preStat = null;
				}
			}
		}
	}
  
  /**
   * get the Access WorkFlow IDs for Current User 
   * @return
   * @throws ApplicationException
   */
  public List getAccessWorkFlowIDs() throws ApplicationException {
    PermissionManager pm = sessionContainer.getPermissionManager();
    Integer userRecordID = sessionContainer.getUserRecordID();
    List userRolesID = pm.getUserRoles();
    List userGroupsID = pm.getUserGroups();
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    String userRoleIDs = "";
    String userGroupIDs = "";
    List permissionWfRecordIDList = new ArrayList();
    List noPermissionWfRecordIDList = new ArrayList();
    synchronized (dbConn) {
      try {
        // get the userGroupIDs
        for (int i = 0; userGroupsID != null && i < userGroupsID.size(); i++) {
          if (i != 0) {
            userGroupIDs += ", ";
          }
          UserGroup group = (UserGroup) userGroupsID.get(i);
          userGroupIDs += group.getID();
        }

        // get the userRoleIDs
        for (int i = 0; userRolesID != null && i < userRolesID.size(); i++) {
          if (i != 0) {
            userRoleIDs += ", ";
          }
          UserRole role = (UserRole) userRolesID.get(i);
          userRoleIDs += role.getID();
        }

        // get possible Workflow RecordID
        sqlStat.append("SELECT  DISTINCT WR.ID, MWSUA.PERMISSION, MWSUA.ACTOR_TYPE, MWSUA.ACTOR_ID \n");
        sqlStat.append("FROM    WORKFLOW_STEP WS, WORKFLOW_RECORD WR, MTM_WF_STEP_USER_ACTOR MWSUA ,SETUP_OPTION_WORKFLOW_CATEGORY SOWC \n");
        sqlStat.append("WHERE   WS.ACTION_TYPE = ? AND \n");
        sqlStat.append("        MWSUA.ACTION_TYPE = ? AND \n");
        sqlStat.append("        WR.ID = WS.WORKFLOW_RECORD_ID AND \n");
        sqlStat.append("        WR.WORKFLOW_CATEGORY_ID = SOWC.ID AND \n");
        sqlStat.append("        WR.RECORD_STATUS = ? AND \n");
        sqlStat.append("        WR.STATUS = ? AND \n");
        sqlStat.append("        WS.ID = MWSUA.WORKFLOW_STEP_ID AND \n");
        sqlStat.append("        ? >= WR.START_DATE AND \n");
        sqlStat.append("        (? < WR.END_DATE OR WR.END_DATE IS NULL) AND \n");
        sqlStat.append("        (MWSUA.ACTOR_TYPE = ? AND MWSUA.ACTOR_ID = ? OR MWSUA.ACTOR_TYPE = ? \n");

        if (!"".equals(userGroupIDs)) {
          sqlStat.append("         OR MWSUA.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND MWSUA.ACTOR_ID IN (" + userGroupIDs + ") \n");
        }

        if (!"".equals(userRoleIDs)) {
          sqlStat.append("         OR MWSUA.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND MWSUA.ACTOR_ID IN (" + userRoleIDs + ") \n");
        }
        sqlStat.append("        ) ");

        // Form the WHERE clause for filtering.

        // format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Get total number of record return.
        preStat = dbConn.prepareStatement(sqlStat.toString());
        Timestamp currTime = Utility.getCurrentTimestamp();
        Timestamp tmrTime = new Timestamp(currTime.getTime());
        this.setPrepareStatement(preStat, 1, WorkflowStep.ACTION_TYPE_SUBMIT);
        this.setPrepareStatement(preStat, 2, MtmWorkflowStepUserActor.ACTION_TYPE_EXECUTION);
        this.setPrepareStatement(preStat, 3, GlobalConstant.STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStat, 5, currTime);
        this.setPrepareStatement(preStat, 6, tmrTime);
        this.setPrepareStatement(preStat, 7, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
        this.setPrepareStatement(preStat, 8, userRecordID);
        this.setPrepareStatement(preStat, 9, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);

        rs = preStat.executeQuery();
        while (rs.next()) {
          String permission = getResultSetString(rs, "PERMISSION");

          if (!this.checkBindingFormExpried(getResultSetInteger(rs, "ID"))) {
            if (permission.indexOf("w") < 0) {
              if (permissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) < 0 && noPermissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) < 0) {
                permissionWfRecordIDList.add(getResultSetInteger(rs, "ID"));
              }
            } else {
              noPermissionWfRecordIDList.add(getResultSetInteger(rs, "ID"));
              if (permissionWfRecordIDList.indexOf(getResultSetInteger(rs, "ID")) > -1) {
                permissionWfRecordIDList.remove(getResultSetInteger(rs, "ID"));
              }
            }
          }
        }
      
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }

        return permissionWfRecordIDList;
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }

  }
  
  /**
   * check binding form expried
   * @param workflowRecordID
   * @return boolean
   * @throws ApplicationException
   */
  private boolean checkBindingFormExpried(Integer workflowRecordID) throws ApplicationException {
    FormRecordDAObject formRecordDAO = new FormRecordDAObject(this.sessionContainer, this.dbConn);
    
    List bindingFormList = formRecordDAO.getBindFormListByWorkflowRecordID(workflowRecordID);
    if (!Utility.isEmpty(bindingFormList)) {
      for (int i = 0; i < bindingFormList.size(); i++) {
        FormRecord formRecord = (FormRecord)bindingFormList.get(i);
        if (!Utility.isEmpty(formRecord.getEndDate()) && formRecord.getEndDate().before(Utility.getCurrentTimestamp())) {
          return true;
        }
      }
    }  
    
    return false;
  }
  
  /**
   * check execute permission by workflow record ID and user record ID 
   * @param workflowRecordID
   * @param userRecordID
   * @return boolean
   * @throws ApplicationException
   */
  public boolean checkPermissionByWorkflowRecordIDUserRecordID (Integer workflowRecordID, Integer userRecordID, String permission) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    
    UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
    UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
    
    List userRolesList = userRoleDAO.getListByUserRecordID(userRecordID);
    List userGroupsList = userGroupDAO.getListByUserRecordIDGroupType(userRecordID,UserGroup.GROUP_TYPE_PUBLIC);

    String userGroupIDs = "-1";
    String userRoleIDs = "-1";

    for (int i = 0; userGroupsList != null && i < userGroupsList.size(); i++) {
      userGroupIDs += ", ";

      UserGroup group = (UserGroup) userGroupsList.get(i);
      userGroupIDs += group.getID();
    }

    for (int i = 0; userRolesList != null && i < userRolesList.size(); i++) {
      userRoleIDs += ", ";
      UserRole role = (UserRole) userRolesList.get(i);
      userRoleIDs += role.getID();
    }

    List progressIDList = new ArrayList();
    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID ");
        sqlStat.append("FROM MTM_WF_STEP_USER_ACTOR A, WORKFLOW_STEP B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("       AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append("       AND A.WORKFLOW_STEP_ID = B.ID ");
        sqlStat.append("       AND B.STEP_SEQ = 1 ");
        sqlStat.append("       AND B.WORKFLOW_RECORD_ID = ? ");
        sqlStat.append("       AND ( ( A.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_USER + "' AND A.ACTOR_ID = ? )");
        sqlStat.append("            OR ( A.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE + "' AND A.ACTOR_ID IN (" + userRoleIDs + ")) ");
        sqlStat.append("            OR ( A.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP + "' AND A.ACTOR_ID IN (" + userGroupIDs + ")) ");
        
        if ("W".equals(permission)) {          
          sqlStat.append("            OR ( A.ACTOR_TYPE = '" + MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE + "') ");  
        }
                
        sqlStat.append("            ) ");
        

        if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_MYSQL) {
          sqlStat.append("AND  BINARY A.PERMISSION LIKE  ? ");
        } else {
          sqlStat.append("AND  A.PERMISSION LIKE  ? ");
        }
        log.debug("----------------------------------------" + sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        this.setPrepareStatement(preStat, 3, userRecordID);
        this.setPrepareStatement(preStat, 4, this.getFormattedKeyword(permission, "LIKE"));

        rs = preStat.executeQuery();
        if (rs.next()) {
          return true;
        }
      
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }
    
    return false;
  }
  
  protected synchronized List getList(Integer workflowGroupID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized (dbConn) {
      try {
        sqlStat
            .append("SELECT A.ID, A.WORKFLOW_CODE, A.WORKFLOW_VERSION, A.WORKFLOW_GROUP_ID, A.ALLOW_INTERRUPT, A.INTERRUPT_WORKFLOW_RECORD_ID, A.STATUS, A.START_DATE, A.END_DATE, A.DESCRIPTION, A.WORKFLOW_CATEGORY_ID, A.DMS_PARENT_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.WORKFLOW_CATEGORY_NAME ");
        sqlStat.append("FROM   WORKFLOW_RECORD A, SETUP_OPTION_WORKFLOW_CATEGORY B ");
        sqlStat.append("WHERE  A.WORKFLOW_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
        sqlStat.append("AND A.WORKFLOW_GROUP_ID = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowGroupID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowRecord tmpWorkflowRecord = new WorkflowRecord();
          tmpWorkflowRecord.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowRecord.setWorkflowCode(getResultSetString(rs, "WORKFLOW_CODE"));
          tmpWorkflowRecord.setWorkflowVersion(getResultSetString(rs, "WORKFLOW_VERSION"));
          tmpWorkflowRecord.setWorkflowGroupID(getResultSetInteger(rs, "WORKFLOW_GROUP_ID"));
          tmpWorkflowRecord.setAllowInterrupt(getResultSetString(rs, "ALLOW_INTERRUPT"));
          tmpWorkflowRecord.setInterruptWorkflowRecordID(getResultSetInteger(rs,
              "INTERRUPT_WORKFLOW_RECORD_ID"));
          tmpWorkflowRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpWorkflowRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpWorkflowRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpWorkflowRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowRecord.setWorkflowCategoryID(getResultSetInteger(rs, "WORKFLOW_CATEGORY_ID"));
          tmpWorkflowRecord.setParentID(getResultSetInteger(rs, "DMS_PARENT_ID"));
          tmpWorkflowRecord.setWorkflowCategory(getResultSetString(rs, "WORKFLOW_CATEGORY_NAME"));
          tmpWorkflowRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
              .getCreatorID()));
          tmpWorkflowRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowRecord
              .getUpdaterID()));
          result.add(tmpWorkflowRecord);
        }
        return (result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {
        } finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {
        } finally {
          preStat = null;
        }
      }
    }
  }
}
